Peta Software.
Sub MIX() ‘m7
x = Range(“A1″): If x “” Then Application.ScreenUpdating = False
wb = “ShaQuR.xls”: n = “‘[SHA.XLS]MAC’!”: n1 = “‘[" & wb & "]MAC’!”: aa = ActiveSheet.Name
t = Timer: w = ActiveCell: c = ActiveCell.Column: r = ActiveCell.Row: s = Selection.Count
p = Range(n & “A4″).End(xlDown).Row + 1: Range(n & “A” & p) = ActiveSheet.Name()
Range(n & “B” & p) = c & ” ” & r & ” ” & s & ” ” & w: Range(n & “C” & p) = Now
If Range(n1 & “E1″) “OK” Then GoTo SHE
If p / 10 – Int(p / 10) = 0 Then Workbooks(“SHA.XLS”).Save
If s > 3 Then Application.Run (“GEN.GEN”): GoTo SHE
‘EOI ——————————————————————————-EOI
SOD:
d = Range(“MIX!A3″).End(xlDown).Row: cts = 0: Range(“A17843:E18743″).ClearContents
If c = 1 And r = 2 Then GoTo SHW ‘sync word in clm A for MIX and SUM
If c = 1 And r > 2 Then GoTo SHF ‘ref sht ALN?
If c = 2 And r = 2 Then GoTo SHA ‘prepare word and check for repeat
If c = 3 And r > 2 Then GoTo SHQ ‘Summary in TEM and 2 Refer to ALQuran
If c = 4 And r = 1 Then GoTo SHR ‘chk arb MIX, TRN, ARC
If c = 6 And r = 2 Then GoTo SHY ‘replace snd code to ARC
If c = 7 And r = 2 Then GoTo SHX ‘general purpose use clm G
If c = 8 Then GoTo SHO ‘produce snd code
If c = 9 And r > 2 Then GoTo SHM ‘Check ROT to CHK
If c = 11 And Range(“Q1″) “” Then GoTo SHS
If c = 16 And r = 1 Then GoTo SHU ‘prep temp PVS and trf to CHK
If c = 17 And r = 1 Then GoTo SHV
If c = 23 Then GoTo SHN ‘prod let words
If c = 25 And r = 2 Then GoTo SHG ‘confirm ROT word to CHK
If c = 25 Then GoTo SHC
If c = 26 And r = 2 Then GoTo SHH ‘place details of BegEnd
If c = 27 Then GoTo SHJ ‘confirm MID BEG ROT END
If c = 29 Then GoTo SHK
If c = 30 And r > 2 Then GoTo SHP
If c = 32 Then GoTo SHT ‘further snd code
If c = 33 And r = 2 Then GoTo SHD ‘AG SHA code putter
If c = 36 And r = 2 Then GoTo SHB ‘replace SND words with TRM
If c = 37 And r = 2 Then GoTo SHI ‘
MsgBox “Nothing To Do”: GoTo SHE
‘———————————————————————————
SHA: If s = 3 Then Stop
‘GoTo tex
d = 17842
If Range(“A1″) = “m” Then Range(“A1″) = “”: Stop ‘Prep wrords & Check for repeat
Range(“LET!H2″).Sort Key1:=Range(“LET!H2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(“A3:E20000″).Font.ColorIndex = 0: Range(“A3″).End(xlDown).Offset(1, 0).Select
Range(“A” & ActiveCell.Row & “:A” & ActiveCell.End(xlDown).Row – 1).EntireRow.Delete
‘For j = 1 To 1
For j = 4 To 4
‘For j = 1 To 3
If j = 1 Then d1 = 3: d2 = 60: d = 60 ‘for test
If j = 2 Then d1 = 6001: d2 = 12000
If j = 3 Then d1 = 12001: d2 = 17842
If j = 4 Then d1 = 3: d2 = 17842
Range(“B” & d1 & “:D” & d2).ClearContents
Range(“A” & d1 & “:A” & d2).Copy: Range(“B” & d1).PasteSpecial xlValues
Selection.TextToColumns Destination:=Range(“B” & d1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=”_”, FieldInfo:=Array(Array(1, 1), Array(2, 1))
With Selection: .HorizontalAlignment = xlLeft: End With
Range(“C” & d1 & “:C” & d2).Copy: Range(“D” & d1).PasteSpecial xlValues
For i = 40 To 47
wha$ = Range(“LET!B” & i): rep$ = “”
Range(“D” & d1 & “:D” & d2).Replace wha$, rep$, xlPart
Next i
Next j
Range(“H3:H” & d).Clear
For j = 1 To 4
Range(“G3:G” & d).Clear
If j = 1 Then pos = “A”: ofs = -6
If j = 2 Then pos = “B”: ofs = -5
If j = 3 Then pos = “C”: ofs = -4
If j = 4 Then pos = “D”: ofs = -3
Range(pos & “2″).Sort Key1:=Range(pos & “2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(“G3:G” & d).FormulaR1C1 = _
“=IF(R[1]C[" & ofs & "]=RC[" & ofs & "],R[1]C+1,0)”
Range(“G3:G” & d).Copy: Range(“G3:G” & d).PasteSpecial xlValues
If j 4 Then GoTo ahs
Range(“H3:H” & d).FormulaR1C1 = “=IF(R[-1]C[-1] 4 Then co = 5
If i = 3 Then co = 3
If i = 2 Then co = 41
If i = 1 Then co = 43
TEN:
Range(“G3:G” & d + 3).Find(i, Range(“G3″), xlFormulas, xlWhole).Activate
r1 = ActiveCell.Row: If r1 = d + 3 Then GoTo TEM
Range(pos & r1 & “:” & pos & r1 + i).Font.ColorIndex = co
Range(“G” & r1 & “:G” & r1 + i) = i + 1: GoTo TEN
TEM: Range(“G3″).Select:
Next i
Next j
tex:
Range(“G3:G” & d).ClearContents
Range(“H2″).Sort Key1:=Range(“H2″), Order1:=xlAscending, _
Key1:=Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(“H3:H” & d).Find(1).Activate: r = ActiveCell.Row
Range(“H3:H” & r).ClearContents
Range(“E3″).Value = 1
Range(“E3:E17842″).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Range(“H3″).End(xlDown).Select
Range(“H” & ActiveCell.Row & “:H” & ActiveCell.Row + 10).EntireRow.Insert
GoTo SHE
‘———————————————————————————
SHB: If s = 3 Then Stop
Range(“F3:F” & d).Copy: Range(“AJ3″).PasteSpecial xlPasteValues
For i = 7 To Range(“CHK!AQ7″).End(xlDown).Row
Range(“AJ3:AJ” & d).Replace Range(“CHK!AQ” & i), Range(“CHK!AX” & i), xlPart
Next i
GoTo SHE
‘———————————————————————————
SHC: If s = 3 Then Stop
Application.ScreenUpdating = True
GoTo tema
Sheets(“PVT”).Select
Range(“A6:A1000″).Find(w).Activate
Application.Run “PVT.PVT”
GoTo SHE
Sheets(“MIX”).Select
ActiveCell.Offset(0, 2).Copy
AppActivate “Program Al Quran”
SendKeys (“{esc 3}%cp{tab 3}{up}{tab 2}{down}{tab 2}~{tab 8}”)
GoTo SHE
tema:
Sheets(“CHK”).Select
Range(“A6:A3000″).Find(w).Activate
GoTo SHE
‘———————————————————————————
SHD: If s = 3 Then Stop ‘GoTo tdm
Range(“AG3:AG” & d).ClearContents
For k = 2 To 3
If k = 2 Then st = 7: fi = 85
If k = 3 Then st = 8: fi = Range(“CHK!A6″).End(xlDown).Row
For i = st To fi
If k = 2 Then w = Range(“CHK!V” & i)
If k = 3 Then w = Range(“CHK!A” & i)
Range(“Y” & d + 2) = w
Range(“Y3″).Select
dhs:
Range(“Y3:Y” & d + 2).Find(w, ActiveCell, xlFormulas, xlWhole, xlByRows, xlNext).Activate
r1 = ActiveCell.Row
If r1 = d + 2 Then GoTo dhu
rj = Range(“CHK!J” & i) ‘P,O,T
ro = Range(“CHK!S” & i) ‘No of MID variation
rt = Range(“CHK!T” & i) ’1, 2 or 3 disturbance code
rp = Range(“CHK!P” & i) ‘Len of WOD
If rp = 1 Then rg = 10: GoTo dhs
If k = 2 Then Range(“AG” & r1) = 20: GoTo dhs
If rp = 2 And rt = 1 Then rg = 21: GoTo dhs
If rp = 2 And rt = 2 Then rg = 22: GoTo dhs
If Left(rj, 1) = “P” Then Range(“AG” & r1) = 31: GoTo dhs
If rj = “O” Or rj = “T” Then Range(“AG” & r1) = 32: GoTo dhs
If ro = 2 Then Range(“AG” & r1) = 33: GoTo dhs
If ro = 1 Then Range(“AG” & r1) = 34: GoTo dhs
If rt = 1 Then Range(“AG” & r1) = 30: GoTo dhs
dhu:
Range(“Y” & d + 2).ClearContents
Next i
Next k
tdm:
Range(“AF2″).Sort Key1:=Range(“AF2″), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
If Range(“AF3″) “x” Then _
Range(“AF3:AF” & Range(“AF2:AF” & d).Find(“x”).Row – 1).ClearContents
Range(“PVT!B2″) = “(All)”
For i = 1 To 1 Step 4
fi = Range(“PVT!A6″).Offset(0, i – 1).End(xlDown).Row – 1
For j = 6 To fi
w = Range(“PVT!A” & j).Offset(0, i – 1)
Range(“D” & d + 2) = w
Range(“D3:D” & d + 2).Find(w, Range(“D3″), xlFormulas, xlWhole, xlByRows, xlNext).Activate
r1 = ActiveCell.Row
If r1 = d + 2 Then GoTo dhv
If Range(“AF” & r1) = “” And Range(“AH” & r1) = ” ” And Range(“AH” & r1) = ” ” _
And Range(“Y” & r1) = Range(“AA” & r1) Then Range(“AF” & r1) = “z” & Len(w)
Range(“AG1″) = “MAK” & i & ” ” & j & “/” & fi & ” ” & r1
dhv:
Next j
Next i
GoTo SHE
‘———————————————————————————
SHF: If s = 3 Then Stop
w = ActiveCell.Offset(0, 2): Sheets(“ALN”).Select
Range(“C3:J6238″).Find(w, Range(“C3″), xlFormulas, xlPart).Activate
GoTo SHE
‘———————————————————————————
SHG: If s = 3 Then Stop
‘GoTo tgm
If Range(“MIX!Y1″) = “r” Then Sheets(“PVT”).Select: Range(“K2″).Select: _
Application.Run “PVT.PVT”: Sheets(“MIX”).Select
r2 = Range(“CHK!A6″).End(xlDown).Row
For k = 1 To 3
If k = 1 Then Range(“CHK!S7:S” & r2).ClearContents: _
Range(“CHK!B7:B” & r2).ClearContents
If k = 1 Then Range(“PVT!B2″) = “(All)”
If k = 2 Then Range(“PVT!B2″) = “z2″
If k = 3 Then Range(“PVT!B2″) = “z3″
fi = Range(“PVT!A7″).End(xlDown).Row – 1
For i = 7 To fi
w = Range(“PVT!A” & i)
If w = “(blank)” Or w = “” Then GoTo ghs
‘If Len(w) = 1 Then GoTo SHE
r = r2 + 1: Range(“CHK!A” & r) = w
r1 = Range(“CHK!A6:A” & r).Find(w, Range(“CHK!A6″), xlFormulas, xlWhole).Row
If r1 < r Then Range(“CHK!A” & r).ClearContents Else r2 = r2 + 1
If k = 1 Then Range(“CHK!A” & r1) = Range(“PVT!A” & i): _
Range(“CHK!B” & r1) = Range(“PVT!B” & i)
Range(“CHK!S” & r1) = k: rt = Range(“CHK!T” & r1)
If rt = “” Then rt = 3
Range(“Y1″) = k & ” ” & i & “/” & fi & ” ” & r1
ghs:
Next i
Next k
tgm:
Sheets(“CHK”).Select
Range(“S6″).Sort Key1:=Range(“S6″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
r1 = Range(“S6″).End(xlDown).Offset(1, 0).Row: r2 = Range(“A6″).End(xlDown).Row
If Range(“A” & r1) “” Then Range(“A” & r1 & “:X” & r2).ClearContents
GoTo SHE
‘———————————————————————————
SHH: If s = 3 Then Stop ‘GoTo the
‘d = 100
Range(“K3:T” & d).ClearContents
fi = Range(“CHK!AC6″).End(xlDown).Row
For i = 7 To fi
wf = Range(“CHK!AC” & i)
‘If Range(“CHK!AA” & i) = “” Or Range(“CHK!AC” & i).Font.ColorIndex = 3 _
Then GoTo hhs
Range(“Z” & d + 2) = wf
Range(“Z3″).Select
HHT:
Range(“Z3:Z” & d + 2).Find(wf, ActiveCell, xlFormulas, xlWhole, xlByRows, xlNext).Activate
rf = ActiveCell.Row
If rf = d + 2 Then GoTo HHS
Range(“K” & rf) = Range(“CHK!Z” & i)
Range(“L” & rf) = Range(“CHK!AA” & i)
Range(“M” & rf) = Range(“CHK!AB” & i)
GoTo HHT
HHS:
Next i
fi = Range(“CHK!AH6″).End(xlDown).Row
For i = 7 To fi
wf = Range(“CHK!AH” & i)
Range(“AB” & d + 2) = wf
Range(“AB3″).Select
hhu:
Range(“AB3:AB” & d + 2).Find(wf, ActiveCell, xlFormulas, xlWhole, xlByRows, xlNext).Activate
rf = ActiveCell.Row
If rf = d + 2 Then GoTo hhv
Range(“P” & rf) = Range(“CHK!AL” & i)
Range(“Q” & rf) = Range(“CHK!AM” & i)
Range(“R” & rf) = Range(“CHK!AN” & i)
Range(“S” & rf) = Range(“CHK!AO” & i)
GoTo hhu
hhv:
‘Range(“Z1″) = “END ” & i & “/” & fi & ” ” & rf
Next i
the:
Range(“N3:N” & d).FormulaR1C1 = _
“=IF(AND(LEN(RC[11])=2,LEN(RC[13])=3),MID(RC[13],2,1),”"”")”
Range(“O3:O” & d).FormulaR1C1 = _
“=IF(AND(LEN(RC[10])=3,LEN(RC[12])=4),MID(RC[12],2,2),”"”")”
Range(“N3:O” & d).Copy
Range(“N3″).PasteSpecial xlPasteValues
‘GoTo SHE
For i = 14 To 15
Cells(2, i).Sort Key1:=Cells(2, i), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
r1 = Range(Cells(2, i), Cells(d, i)).Find(“”).Row
Range(Cells(r1, i), Cells(d, i)).ClearContents
Next i
GoTo SHE
‘———————————————————————————
SHI: If s = 3 Then Stop
For i = 3 To d
For j = 1 To 3
le = Mid(Range(“AE” & i), j, 1)
If j = 1 Then sp = 1
If j = 2 Then sp = 1 + Mid(Range(“AE” & i), j – 1, 1)
If j = 3 Then _
sp = 1 + Mid(Range(“AE” & i), j – 1, 1) + Mid(Range(“AE” & i), j – 2, 1)
Range(“AK” & i).Offset(0, 3 – j) = Mid(Range(“D” & i), sp, le)
Next j
Next i
GoTo SHE
‘———————————————————————————
SHJ: If s = 3 Then Stop
If r = 1 Then GoTo SHE
r2 = r: If r = 2 Then GoTo jhs
For i = r To r + s
If Left(Range(“Y” & i), 1) = “,” Then Range(“Y” & i + 1).Select Else GoTo jht
Application.Run “GEN.GEN”
jht:
Next i
jhs:
r = r2
If r = 2 Then r0 = 3 Else r0 = r
If r = 2 Then r1 = d Else r1 = r + s – 1
Range(“Y” & r0 & “:AI” & r1).Copy: Range(“Y” & r0 & “”).PasteSpecial xlPasteValues
Range(“AA” & r0 & “:AA” & r1).FormulaR1C1 = _
“=MID(RC[2],LEN(RC[1])+1,LEN(RC[2])-LEN(RC[-1])-LEN(RC[1]))”
Range(“AA” & r0 & “:AA” & r1).Copy
Range(“AA” & r0 & “”).PasteSpecial xlValues ‘MID
Range(“G” & r0 & “:G” & r1).FormulaR1C1 = “=RIGHT(RC[22],LEN(RC[19]))”
Range(“G” & r0 & “:G” & r1).Copy
Range(“Z” & r0 & “”).PasteSpecial xlValues ‘END
Range(“G” & r0 & “:G” & r1).FormulaR1C1 = “=Left(RC[22],LEN(RC[21]))”
Range(“G” & r0 & “:G” & r1).Copy
Range(“AB” & r0 & “”).PasteSpecial xlValues ‘BEG
Range(“AH” & r0 & “:AH” & r1).FormulaR1C1 = “=RC[-6]&”" “”&RC[-8]“
Range(“AH” & r0 & “:AH” & r1).Copy
Range(“AH” & r0 & “”).PasteSpecial xlValues ‘BNE
Range(“AI” & r0 & “:AI” & r1).FormulaR1C1 = “=RC[-7]&”" “”&RC[-8]&”" “”&RC[-9]“
Range(“AI” & r0 & “:AI” & r1).Copy
Range(“AI” & r0 & “”).PasteSpecial xlValues ‘WOS
Range(“AE” & r0 & “:AE” & r1).FormulaR1C1 = _
“=LEN(RC[-3])&LEN(RC[-4])&LEN(RC[-5])&”".”"&100+LEN(RC[-27])&”".”"&100+LEN(RC[-28])”
Range(“AE” & r0 & “:AE” & r1).Copy
Range(“AE” & r0 & “”).PasteSpecial xlValues ‘LEN
Range(“G” & r0 & “:G” & r1).FormulaR1C1 = “=IF(RC[21]&RC[20]&RC[19]=RC[22],1,0)”
Range(“G” & r0 & “:G” & r1).Copy
Range(“G” & r0 & “”).PasteSpecial xlValues ‘CHK
Range(“I” & r0 & “:I” & r1).ClearContents
For i = r0 To r1
lf = Len(Range(“Y” & i))
If lf 3 Then GoTo jhu
If lf = 2 Then j1 = 1: J2 = “A”
If lf = 3 Then j1 = 2: J2 = “V”
For j = 1 To j1
For k = 1 To 4
If k = 1 Then le = “Ç”
If k = 2 Then le = “Ê”
If k = 3 Then le = “æ”
If k = 4 Then le = “í”
wm = Left(Range(“Y” & i), j) & le & Right(Range(“Y” & i), lf – j)
wn = Range(“AA” & i)
If wn = wm Then Range(“I” & i) = J2 & k * j
If wn = Range(“Y” & i) Then Range(“I” & i) = J2 & 0
Next k
Next j
jhu:
p1 = Len(Range(“AB” & i)) + 1
p2 = Len(Range(“AC” & i)) – Len(Range(“AB” & i)) – Len(Range(“Z” & i))
If lf = 0 And Range(“AF” & i) = “” Then Range(“Y” & i) = Mid(Range(“AC” & i), p1, p2)
Next i
If r 2 Then GoTo SHE
Range(“X3:X” & d).ClearContents
Range(“Z1″).FormulaR1C1 = “=COUNTA(R[2]C[-3]:R[" & d & "]C[2])”
For i = 23 To 28
Cells(2, i).Sort Key1:=Cells(2, i), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
r1 = Range(Cells(2, i), Cells(d, i)).Find(“”).Row
Range(Cells(r1, i), Cells(d, i)).ClearContents
Next i
Range(“AA” & r1 + 1).Select
Range(“G2″).Sort Key1:=Range(“G3″), Order1:=xlAscending, Key2:=Range(“Y3″) _
, Order2:=xlAscending, Key3:=Range(“AC3″), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
If Range(“G3″) 1 Then Range(“G3″).Select
GoTo SHE
‘———————————————————————————
SHK: If s = 3 Then Stop
If r = 2 Then GoTo SHK1
wr = ” ” & Range(“C” & r) & ” “: r1 = Range(“H” & r) + 2: Sheets(“RED”).Select
If r1 = 2 Then r1 = Range(“SUR!L3:L6300″).Find(wr, Range(“SUR!L3″), , xlPart).Row
Range(“AA2″) = Range(“SUR!L” & r1): Range(“AB2″) = Range(“SUR!AD” & r1)
Range(“AB4″) = Range(“SUR!T” & r1): Range(“AA1″) = Range(“MIX!C” & r)
GoTo SHE
SHK1:
For i = Range(“H1″) To Range(“A3″).End(xlDown).Row
If Range(“AC” & i) = Range(“AC” & i – 1) Then GoTo khs1 Else wr = ” ” & Range(“C” & i) & ” “
Range(“H” & i) = Range(“SUR!A” & Range(“SUR!L3:L6300″).Find(wr, Range(“SUR!L3″), , xlPart).Row)
Range(“H1″) = i
khs1: Next i
GoTo SHE
‘———————————————————————————
SHL: If s = 3 Then Stop
r1 = Range(“R3″).End(xlDown).Row + 1: Range(“R” & r1 & “:V” & r1 + 2).Delete xlUp
rgh = “T3:T” & Range(“T3″).End(xlDown).Row: Range(rgh).ClearContents: Range(“T1″) = w
Range(rgh).FormulaR1C1 = “=if(or(EXACT(RC[1],R1C20),EXACT(RC[2],R1C20)),1,0)”
Range(rgh).Copy: Range(“T3″).PasteSpecial xlValues
Range(“T3″).Sort Key1:=Range(“T3″), Order1:=xlDescending, Header:=xlGuess
r1 = Range(rgh).Find(0, Range(“T3″), xlValues, xlWhole).Row
Range(rgh).ClearContents: Range(“R” & r1 & “:V” & r1 + 2).Insert xlDown
GoTo SHE
‘———————————————————————————
SHM: If s = 3 Then Stop
w = Range(“Y” & r)
Sheets(“CHK”).Select
Range(“A6:A” & Range(“A6″).End(xlDown).Row).Find(w, Range(“A6″), xlFormulas, xlWhole).Activate
GoTo SHE
‘———————————————————————————
SHN: If s = 3 Then Stop
Range(“AA3:AA” & d).Copy
Range(“W3″).PasteSpecial xlPasteValues
si = 3: fi = 38: rw = “W3:W” & d
For i = si To fi
If Range(“LET!J” & i) = “” Then GoTo ohu
wha$ = Range(“LET!B” & i): rep$ = “”
Range(rw).Replace wha$, rep$, xlPart
ohu:
Next i
Range(“W2″).Sort Key1:=Range(“W2″), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
r1 = Range(“W2:W” & d).Find(“”).Row
Range(“W” & r1 & “:W” & d).ClearContents
GoTo SHE
‘———————————————————————————
SHO: If s = 3 Then Stop
If r = 1 Then GoTo oha
If r = 2 Then GoTo ohb
GoTo SHE
oha:
GoTo SHE
ohb:
‘d = 1000
rgx = “I3:I” & d: rgy = “”: rgz = “C3:C” & d
Sheets(“MIX”).Select:
If c = 9 Then Range(rgz).Copy
Range(“I3″).PasteSpecial xlValues
stt = 3: fin = 38
For i = stt To fin
If Range(“LET!J” & i) = “x” Then GoTo oht
wha$ = Range(“LET!B” & i): rep$ = “Ï”
Range(rgx).Replace wha$, rep$, xlPart
oht: Next i:
‘GoTo SHE
stt = 60: fin = 82
For i = stt To fin
wha$ = Range(“LET!B” & i): rep$ = Range(“LET!C” & i)
Range(rgx).Replace wha$, rep$, xlPart
Next i
GoTo SHE
‘———————————————————————————
ohs: ‘not used anymore
Range(rgx).Replace Range(“LET!F86″), “x”, xlPart
Range(rgx).Replace Range(“LET!F85″), Range(“LET!F85″) & ” “, xlPart
Range(rgx).Replace “X”, Range(“LET!F86″), xlPart
For i = 50 To 53 ‘mati dan tanwin
wha$ = Range(“LET!B” & i): rep$ = Range(“LET!B” & i) & ” “
Range(rgx).Replace wha$, rep$, xlPart
Next i
Sheets(“LET”).Select
‘ nun mati and mad ya wau
rge = “MIX!” & rgx: stt = 85: fin = 94: fij = 4
For j = 2 To fij
For k = 1 To 3
For i = stt To fin
If k = 1 Then wha = Cells(i, j): rep = Cells(i, 1)
If k = 2 Then wha = Cells(84, j): rep = Cells(84, j) & ” “
If k = 3 Then wha = Cells(i, 1): rep = Cells(i, j)
Range(rge).Replace wha, rep, xlPart, xlByRows, MatchCase:=True
Next i
Next k
Next j
Sheets(“MIX”).Select: Range(“Y3:AE20000″).Clear
Range(rge).Copy: Range(“Y3″).Select: ActiveSheet.Paste: SendKeys (“~”)
Selection.TextToColumns Destination:=Range(“Y3″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
Range(“Y3:Y20000″).Cut: Range(“AE3″).Select: ActiveSheet.Paste
Range(“AD3:AD20000″).Cut: Range(“Y3″).Select: ActiveSheet.Paste
Range(“Z3:Z20000″).Cut: Range(“AD3″).Select: ActiveSheet.Paste
Range(“AC3:AC20000″).Cut: Range(“Z3″).Select: ActiveSheet.Paste
Range(“AA3:AA20000″).Cut: Range(“AC3″).Select: ActiveSheet.Paste
Range(“Y3:Z20000″).Cut: Range(“Z3″).Select: ActiveSheet.Paste
Range(“Y3:AD20000″).Select
With Selection.Font: .Name = “Tahoma (Arabic)”: .Size = 10: End With
With Selection: .Orientation = 0: End With
GoTo SHE
For i = 25 To 29
Cells(3, i).Sort Key1:=Cells(3, i), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Next i
GoTo SHE
‘———————————————————————————
SHP: If s = 3 Then Stop
w = Range(“C” & r): Sheets(“ARC”).Select
Range(“F3:ED6238″).Find(w, Range(“F3″), xlFormulas, xlWhole, xlByRows).Activate
Application.Run “ARC.ARC”
GoTo SHE
‘———————————————————————————
SHQ: If s = 3 Then Stop
If s = 2 Then GoTo SHQ1
w = ” ” & w & ” “: Range(“SUR!I1″) = w: Sheets(“SUR”).Select
Range(“A2:AL6240″).Sort Key1:=Range(“I3″), Order1:=xlAscending, Key2:= _
Range(“A3″), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
If Range(“TEM!A1″) = “” Then Range(“TEM!A3:H5500″).ClearContents
If Range(“TEM!A1″) = “” Or Range(“TEM!A3″) = “” Then sl = 3 Else _
If Range(“TEM!A4″) = “” Then sl = 4 Else sl = Range(“TEM!A3″).End(xlDown).Row + 1
nb = Range(“SUR!J1″) + 2: cm = “AA” ‘L
Range(cm & “3:” & cm & nb).Copy: Range(“TEM!A” & sl).PasteSpecial xlValues
Range(“A3:A” & nb).Copy: Range(“TEM!B” & sl).PasteSpecial xlValues
Range(“H3:H” & nb).Copy: Range(“TEM!C” & sl).PasteSpecial xlValues
Range(“AC3:AC” & nb).Copy: Range(“TEM!E” & sl).PasteSpecial xlValues
Range(“TEM!D” & sl & “:D” & sl + Range(“SUR!J1″) – 1) = w
Sheets(“TEM”).Select: ActiveWindow.DisplayHeadings = False: Range(“C1″).Select
GoTo SHE
SHQ1:
If Range(“A1″) = “m” Then Stop
ActiveCell.Copy: AppActivate “Program Al Quran”
SendKeys (“{esc 3}%cp{tab 3}{up}{tab 4}~{tab 8}”), True
GoTo SHE
‘———————————————————————————
SHR: If s = 3 Then Stop
For i = 7930 To d
w = Range(“B” & i)
r = Range(“ARC!F3:ED6238″).Find(w).Row
c = Range(“ARC!F3:ED6238″).Find(w).Column
w1 = Range(“TRM!A” & r).Offset(0, c – 1)
Range(“AA” & i) = r & ” ” & c: Range(“AC” & i) = w1
m = Range(“SUM!A3″).End(xlDown).Row + 1: Range(“SUM!A” & m) = w
If Range(“SUM!A3:A” & m).Find(w, Range(“SUM!A3″)).Offset(0, 10) = w1 _
Then Range(“SUM!A” & m).Clear Else Range(“SUM!K” & m) = _
Range(“SUM!A3:A” & m).Find(w, Range(“SUM!A3″)).Offset(0, 10)
If Range(“A” & i) w1 Then Range(“A3″).End(xlDown).Offset(1, 0) = w1: _
Range(“A3″).End(xlDown).Offset(0, 1) = w
rhs:
Next i
GoTo SHE
‘———————————————————————————
SHT: If s = 3 Then Stop
Range(“AG3:AG20000″).Copy: Range(“AF3″).PasteSpecial xlValues
For j = 3 To 100
‘If Range(“LET!C” & j) = “x” Or Range(“LET!A” & j) = “” Then GoTo ths
Range(“AF3:AF20000″).Replace Range(“PVS!J” & j), j, xlPart, xlByRows, MatchCase:=True
ths:
Next j
For i = 1 To 5
If i = 1 Then w5 = “Ç”
If i = 2 Then w5 = “Ô
If i = 3 Then w5 = “È”
If i = 4 Then w5 = “Ê”
If i = 5 Then w5 = “Ë”
Range(“AF3:AF20000″).Replace w5, “”, xlPart, xlByRows, MatchCase:=True
Next i
Range(“AF2″).Sort Key1:=Range(“AF2″), Order1:=xlAscending, _
Key2:=Range(“AG2″), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
GoTo SHE
‘———————————————————————————
SHU: If s = 3 Then Stop ‘GoTo uhs
Sheets.Add: ActiveSheet.Name = “PVS”
Range(“F3″) = “a”: Range(“G3″) = “b”: Range(“G4″) = “1″: Range(“F4″) = “a”
Range(“F3:G4″).Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
“PVS!R3C6:R4C7″, TableDestination:=”R4C9″, TableName:=”PArOne”
ActiveSheet.PivotTables(“PArOne”).AddFields RowFields:=”a”
ActiveSheet.PivotTables(“PArOne”).PivotFields(“b”).Orientation = xlDataField
Range(“MIX!I1″).FormulaR1C1 = “=COUNTA(R[2]C[2]:R[17841]C[11])”
Range(“MIX!I1″).Copy: Range(“MIX!I1″).PasteSpecial xlValues
Range(“PVS!F2″) = “x”: Range(“PVS!F3″) = “a”: Range(“PVS!G3″) = “b”
Range(“PVS!G2″).FormulaR1C1 = “=SUM(R[2]C:R[19998]C)”
Range(“PVS!F4:H20000″).ClearContents
For i = 1 To 8
Sheets(“PVS”).Select
ActiveSheet.PivotTables(“ParOne”).PivotSelect “”, xlDataAndLabel
Selection.ClearContents
Sheets(“MIX”).Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
“MIX!R2C11:R18000C18″, TableDestination:=”[ShaQuS.xls]PVS!R2C1″, TableName _
:=”ParOne”
ActiveSheet.PivotTables(“ParOne”).AddFields RowFields:=i
ActiveSheet.PivotTables(“ParOne”).PivotFields(i).Orientation = _
xlDataField
Range(“A4:B” & Range(“A4″).End(xlDown).Row – 2).Copy
Range(“F2″).End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Next i:
uhs:
Sheets(“PVS”).Select
ActiveSheet.PivotTables(“ParOne”).PivotSelect “”, xlDataAndLabel
Selection.ClearContents
Range(“F3:G” & Range(“F3″).End(xlDown).Row).Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
“PVS!R3C6:R3978C7″, TableDestination:=”R2C1″, TableName:=”ParOne”
ActiveSheet.PivotTables(“ParOne”).AddFields RowFields:=”a”
ActiveSheet.PivotTables(“ParOne”).PivotFields(“b”).Orientation = _
xlDataField
ActiveSheet.PivotTables(“ParOne”).PivotSelect “‘ð’”, xlDataAndLabel
Range(“A4:B” & Range(“A4″).End(xlDown).Row – 1).Copy
Range(“CHK!AQ7″).PasteSpecial xlValues
Sheets(“CHK”).Select: Range(“AT6″).Select
Application.Run Macro:=”CHK.CHK”
GoTo SHE
‘———————————————————————————
SHS: If s = 3 Then Stop
If Range(“O1″) > 20 Then Application.ScreenUpdating = False
For i = ActiveCell.Row To ActiveCell.Row + Range(“O1″)
Range(“L” & i & “:Z” & i).ClearContents: w4 = “”
flg = 0: Range(“C” & i).Copy: Range(“K” & i).PasteSpecial Paste:=xlValues
Range(“J” & i).FormulaR1C1 = “=LEN(RC[1])”: Range(“J” & i).Copy
Range(“J” & i).PasteSpecial Paste:=xlValues
Range(“L” & i & “:AL” & i).ClearContents
Range(“K” & i).TextToColumns Destination:=Range(“L” & i), DataType:=xlFixedWidth _
, FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array( _
18, 1), Array(19, 1), Array(20, 1))
For j = 12 To 12 + Range(“J” & i)
ats = “ó”: bwh = “ö”: dep = “õ”: mat = “ú”: sab = “ø”: alf = “Ç”: yaa = “í”: wau = “æ”
nun = “ä”: lam = “á”: alg = “Ô: baa = “È”: taa = “Ê”: saa = “Ë”
ann = “ð”: inn = “ò”: unn = “ñ”
c1 = j + 1: c2 = j + 2: c3 = j + 3: t1 = j – 1: t2 = j – 2: t3 = j – 1: q = “”
If Cells(i, j) = mat _
Then Cells(i, j) = Cells(i, j) & ” “: _
w4 = w4 & q & “m”
If Cells(i, j) = sab Then Cells(i, t1) = Cells(i, t1) & mat & ” ” & Cells(i, t1): _
Cells(i, j).ClearContents: w4 = w4 & q & “s”: GoTo ddd
ddd:
If Cells(i, j) = alf And Cells(i, c1) = nun _
And Cells(i, c2) ats And Cells(i, c2) bwh And Cells(i, c2) dep _
And Cells(i, c2) sab And Cells(i, c2) mat _
And Cells(i, c2) ann And Cells(i, c2) inn And Cells(i, c2) unn _
Then Cells(i, c1) = Cells(i, c1) & ” “: w4 = w4 & q & “b”: GoTo eee
If Cells(i, j) = alf And Cells(i, c1) = lam _
And Cells(i, c2) = mat _
Then Cells(i, c2) = Cells(i, c2) & ” “: w4 = w4 & q & “c”: GoTo eee
If Cells(i, j) = alf And Cells(i, c1) = lam And Cells(i, t1) = ats _
And Cells(i, c3) sab And Cells(i, c3) mat _
Then Cells(i, j) = Cells(i, j) & ” “: w4 = w4 & q & “d”: GoTo eee
If Cells(i, j) = alf And Cells(i, t1) = ats _
And Cells(i, c1) lam _
And Cells(i, t2) sab And Cells(i, c2) sab And Cells(i, c2) mat _
Then Cells(i, j) = Cells(i, j) & ” “: w4 = w4 & q & “a”
eee: If Cells(i, j) = wau And Cells(i, t1) = dep And Cells(i, c1) sab _
And Cells(i, c1) mat And Cells(i, c1) ats _
Then Cells(i, j) = Cells(i, j) & ” “: w4 = w4 & q & “w”
If Cells(i, j) = yaa And Cells(i, t1) = bwh And Cells(i, c1) sab _
And Cells(i, c1) ats _
And Cells(i, c1) mat And Cells(i, c2) alf And Cells(i, t2) wau _
Then Cells(i, j) = Cells(i, j) & ” “: w4 = w4 & q & “y”
If Cells(i, j) = nun And Cells(i, t1) alf _
And Cells(i, c1) ats And Cells(i, c1) bwh And Cells(i, c1) dep _
And Cells(i, c1) sab And Cells(i, c1) mat _
And Cells(i, c1) ann And Cells(i, c1) inn And Cells(i, c1) unn _
Then Cells(i, j) = Cells(i, j) & ” “: w4 = w4 & q & “n”
Next j
w = “”
For j = 12 To 12 + Range(“J” & i)
w = w & Cells(i, j)
Next j
Range(“K” & i) = Trim$(w): Range(“L” & i & “:AL” & i).ClearContents
Range(“J” & i).ClearContents:
Range(“K” & i).TextToColumns Destination:=Range(“K” & i), DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
Range(“J” & i) = w4
Next i
ActiveCell.Offset(0, 1).Select: ActiveCell.Copy
Range(“Q1″) = ActiveCell.Row: Range(“Q1″).Select
Application.Run Macro:=”MIX.MIX”
GoTo SHE
‘———————————————————————————
SHV: If s = 3 Then Stop
If Range(“Q1″) “” Then r1 = Range(“Q1″): r0 = r1 – Range(“O1″) Else _
r0 = 3: r1 = Range(“A3″).End(xlDown).Row
For j = 11 To 16
Range(Cells(r0, j), Cells(r1, j)).Copy: Range(“Q” & r0).PasteSpecial xlValues
Range(“R1″).FormulaR1C1 = “ú”
Range(“S1:T1″).Copy: Range(“S” & r0 & “:T” & r1).Select: ActiveSheet.Paste
Selection.Copy: Selection.PasteSpecial xlValues
Range(“T” & r0 & “:T” & r1).Copy: Range(“U” & r0).Select: ActiveSheet.Paste
For k = 1 To 6
If k = 1 Then w2 = “ó” ‘ats
If k = 2 Then w2 = “ö” ‘bwh
If k = 3 Then w2 = “õ” ‘dep
If k = 4 Then w2 = “ð” ‘ann
If k = 5 Then w2 = “ò” ‘inn
If k = 6 Then w2 = “ñ” ‘unn
Range(“U” & r0 & “:U” & r1).Replace w2, w2 & ” “, xlPart
Next k
Range(“R” & r0 & “:R” & r1).FormulaR1C1 = “=TRIM(RC[3]& “” “” &RC[1])”
Range(“R” & r0 & “:R” & r1).Copy: Range(“R” & r0).PasteSpecial xlValues
Range(“S” & r0 & “:U” & r1).ClearContents
Range(“R” & r0 & “:R” & r1).Copy: Cells(r0, j).PasteSpecial xlValues
Next j
Range(“F” & r0 & “:F” & r1).FormulaR1C1 = _
“=trim(RC[5]& “” “”&RC[6]& “” “”&RC[7]& “” “”&RC[8]& “” “”&RC[9]& “” “”&RC[10])”
Range(“F” & r0 & “:F” & r1).Copy
Range(“F” & r0).PasteSpecial xlValues
Range(“K” & r0 & “:T” & r1).ClearContents
Selection.TextToColumns Destination:=Range(“K” & r0), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
Range(“G” & r0 & “:G” & r1).FormulaR1C1 = “=COUNTA(RC[4]:RC[13])”
Range(“G” & r0 & “:G” & r1).Copy
Range(“G” & r0).PasteSpecial xlValues
For j = r0 To r1
w = “”
For i = 1 To Range(“G” & j)
w = w & ” ” & Range(“K” & j).Offset(0, Range(“G” & j) – i)
Next i
Range(“I” & j) = Trim(w)
Next j
Range(“K” & r1 + 1).Select
If r1 + Range(“O1″) > 17842 Or Range(“O1″) = 20 Then GoTo SHE Else GoTo SHS
GoTo SHE
‘———————————————————————————
SHW: If s = 3 Then Stop ‘sync word in clm A for MIX and SUZ
Sheets(“SUZ”).Select
Range(“A1:A3″).EntireRow.Delete
Range(“A2″).Sort Key1:=Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets(“MIX”).Select
Range(“A2″).Sort Key1:=Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For j = 1 To 2
If j = 1 Then nam = “MIX”: nan = “SUZ” Else nam = “SUZ”: nan = “MIX”
For i = Range(“A1″) – 1 To Range(nam & “!A2″).End(xlDown).Row
w1 = Range(nam & “!A” & i):
Range(“G” & i) = i
r1 = Range(nan & “!A2″).End(xlDown).Row + 1
Range(nan & “!A” & r1) = w1
r2 = Range(nan & “!A2:A” & r1).Find(w1, Range(nan & “!A2″), xlWhole).Row
If r2 0 Then GoTo xhb ‘to skip clearing of clm G
Range(“G3:G” & Range(“G2″).End(xlDown).Row).ClearContents
xhb:
r1 = 3
If rg = “p” Then Sheets(“PVT”).Select: ActiveCell.Offset(1, 0).Select: _
w = ActiveCell: c = ActiveCell.Column: GoTo xhf
If rg = “t” Then cn = Range(“CHK!AA2″) + 1: w = Range(“CHK!AA” & cn): _
Range(“CHK!AA2″) = cn: GoTo xhf
If rg = “c” Then cn = Range(“CHK!AQ2″) + 1: w = Range(“CHK!AQ” & cn) & ” “: _
Range(“CHK!AQ2″) = cn Else: w = rg
‘the next lines prepares the variation of a basic word with a,t,w,y
‘CQ51 for 3-let-word and CQ46 for 2-let
xhf:
Range(“CHK!CQ51″) = w: Range(“CHK!CQ46″) = w: l = Len(w)
Range(“CHK!CR48:CU49″).Copy: Range(“CHK!CR51″).PasteSpecial xlValues
If rg = “c” Then ps = “F”
If rg = “p” Then ps = “D”
If rg = “t” Then ps = “A” Else ps = “D”
Sheets(“MIX”).Select:
If rg = “p” Or rg = “c” Or rg = “t” Or l > 3 Then _
st = 51: fi = 51: fj = 0: fk = 0: GoTo xhe
If l = 2 Then st = 46: fi = 46 Else st = 51: fi = 52
fj = 4: fk = 3
xhe:
ss = 0: su = 1
For i = st To fi
For j = ss To fj
w = Range(“CHK!CQ” & i).Offset(0, j)
If w = “” Then GoTo xha
Range(ps & d + 2) = w: Range(ps & r1).Select
xhr:
Range(ps & r1 & “:” & ps & d + 2).Find(w, ActiveCell, xlFormulas, xlPart).Activate
r = ActiveCell.Row
If r = d + 2 Then Range(ps & d + 2).Clear: GoTo xha
If rg = “p” Or rg = “c” Then GoTo xhc
If Len(rg) = 3 Then GoTo xhc
If Range(“MIX!F1″) = “” Then Range(“X” & r).ClearContents
td = Range(“X” & r) ‘previous content of cells
‘If Range(“Y” & r) = rg Then Range(“X” & r) = td & rg & 0
If Range(“Y” & r) = w Then Range(“X” & r) = td & w
If Len(Range(“Y” & r)) 3 Then GoTo xhc
For k = su To fk
m = Mid(Range(“Y” & r), k, 1)
If k = 1 Then n = Trim(Right(Range(“Y” & r), 2))
If k = 2 Then n = Trim(Left(Range(“Y” & r), 1) & Right(Range(“Y” & r), 1))
If k = 3 Then n = Trim(Left(Range(“Y” & r), 2))
te = Range(“X” & r) ‘previous content of cells
‘opt1
If (m = “Ç” Or m = “Ê” Or m = “æ” Or m = “í”) And n = rg Then _
Range(“X” & r) = te & rg & k ‘a,t,w,y
If (m = “ä” Or m = “Ô Or m = “Å” Or m = “”) And n = rg Then _
Range(“X” & r) = te & rg & k + 3 ‘n,a-etc
‘opt2
‘If (m = “Ç” Or m = “Ê” Or m = “æ” Or m = “í”) And n = rg Then _
Range(“X” & r) = te & rg & k ‘a,t,w,y
‘If (m = “ä” Or m = “Ô Or m = “Å” Or m = “”) And n = rg Then _
Range(“X” & r) = te & rg & k + 3 ‘n,a-etc
Next k
xhc: ‘for 2-let-words
tf = Range(“G” & r) ‘previous content of cells
‘opt1
‘If Range(“Y” & r) = rg Then Range(“G” & r) = tf & Range(“G” & 1) & 1 _
Else Range(“G” & r) = tf & Range(“G” & 1) & 2
‘opt2
‘ c is catering for PVT columns
If Range(“Y” & r) = rg Or ((rg = “p” Or rg = “c”) And _
Range(“Y” & r).Offset(0, (c – 1) / 2) = w) _
Then Range(“X” & r) = tf & w: Range(“G” & r) = tf & 1 _
Else Range(“X” & r) = tf & w: Range(“G” & r) = tf & 2
‘opt3
‘If Range(“Y” & r) = rg Or ((rg = “p” Or rg = “c”) And _
Range(“Y” & r).Offset(0, (c – 1) / 2) = w) _
Then Range(“G” & r) = tf & 1 Else Range(“G” & r) = tf & 2
GoTo xhr
xha:
Next j
Next i
xhs:
ss0 = Range(“X1″)
If ss0 = “bn” Then ss1 = Range(“AB2″): ss2 = Range(“AH2″)
If ss0 = “br” Then ss1 = Range(“AB2″): ss2 = Range(“Y2″)
If ss0 = “cr” Then ss1 = Range(“I2″): ss2 = Range(“Y2″)
If ss0 = “eb” Then ss1 = Range(“Z2″): ss2 = Range(“AB2″)
If ss0 = “rm” Then ss1 = Range(“Y2″): ss2 = Range(“AA2″)
If ss0 = “tc” Then ss1 = Range(“X2″): ss2 = Range(“I2″)
If ss0 = “tr” Then ss1 = Range(“X2″): ss2 = Range(“Y2″)
Range(“G2″).Sort Key1:=Range(“G2″), _
Order1:=xlAscending, Key2:=ss1, Order2:=xlAscending, _
Key3:=ss2, Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
‘for safety if no data is found
If Range(“G3″) “” Then r1 = Range(“G2″).End(xlDown).Row Else _
Range(“G3″) = 1: r1 = 4
Range(“G1″).End(xlDown).Select: Range(“H1″) = w: Range(“I1″) = c
GoTo SHE
xht:
For j = 3 To d
w = “”
Range(“F” & j).Copy: Range(“AA” & j).PasteSpecial xlValues
Range(“AA” & j).TextToColumns Destination:=Range(“AA” & j), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
For i = 1 To Range(“U” & j)
w = w & ” ” & Range(“AA” & j).Offset(0, Range(“U” & j) – i)
Next i
Range(“G” & j) = Trim(w): Range(“AA” & j & “:az” & j).Clear
Next j
GoTo SHE
xhu:
MsgBox (“Arrange Wa,aA,Ka,Fa”)
Range(“G3:G” & d).FormulaR1C1 = _
“=IF(OR(LEFT(RC30,3)=”"Wao”",LEFT(RC30,3)=”"FAo”"),”"a”",”"”")”
‘ “=IF(OR(LEFT(RC30,3)=”"Wao”",LEFT(RC30,3)=”"aAo”",LEFT(RC30,3)=”"KAo”",LEFT(RC30,3)=”"FAo”"),”"a”",”"”")”
Range(“G3:G” & d).Copy: Range(“G3″).PasteSpecial xlValues
Range(“G2″).Sort Key1:=Range(“G2″), Key2:=Range(“AB2″), Key3:=Range(“AD2″), Header:=xlGuess
r3 = Range(“G3:G” & d).Find(“”).Row: Range(“G2″).Select
GoTo SHE
xhv:
‘Range(“F3:F” & d).FormulaR1C1 = “=RC[19]&”" “” &”RC[9]&”" “”&RC[22]&”" “”&RC[20]“
Range(“F3:F” & d).Copy
Range(“F3″).PasteSpecial xlValues
GoTo SHE
xhw:
Range(“C” & d + 1 & “:C” & d + 11).EntireRow.Delete
e = Range(“C3″).End(xlDown).Row
Range(“A3:A” & e).FormulaR1C1 = “=RC[1]&”"_”"&RC[2]“
Range(“A3:A” & e).Copy
Range(“A3″).PasteSpecial xlPasteValues
GoTo SHE
xhx:
‘d = 100
‘Range(“G3:G” & d).FormulaR1C1 = _
“=RC[-2]&”" “”&RC[-3]&”" / “”&RC[16]&”" “”&RC[18]&”" / “”&RC[21]&”" “”&RC[20]&”" “”&RC[19]“
fl = “=RC[-2]&”" “”&”
For i = 1 To 25
fl = fl & “RC[" & i & "]&”" “”&”
Next i
Range(“G3:G” & d).FormulaR1C1 = fl & “RC[26]“: Range(“G3:G” & d).Copy
Range(“G3″).PasteSpecial xlPasteValues: Range(“H3:AG” & d).ClearContents
GoTo SHE
xhy:
Range(“E2″).Sort Key1:=Range(“E2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(“E2:V” & d).Copy: Workbooks.Open Filename:=”C:\WINDOWS\Personal\MixDat.xls”
Sheets(“MID”).Select: Range(“A1″).PasteSpecial xlPasteValues: ActiveWorkbook.Save
ActiveWindow.Close: Range(“J3:U” & d).ClearContents
GoTo SHE
xhz:
ActiveCell.End(xlDown).Select: r1 = ActiveCell.Row – 1: Range(“W” & r1 & “:AB” & r1).Copy
xhz1: If Range(“AC” & r1 + 1) = Range(“AC” & r1) Then _
Range(“W” & r1 + 1).PasteSpecial xlPasteAll: r1 = r1 + 1: GoTo xhz1
Range(“G” & ActiveCell.Row).Select: GoTo xhz
GoTo SHE
‘———————————————————————————
SHY: If s = 3 Then Stop ‘Replace snd wrd to ARC
Sheets(“ARC”).Select
For i = Range(“MIX!F1″) To 14426
w1 = Range(“MIX!C” & i): w2 = Range(“MIX!F” & i)
Range(“J1″) = w1: Range(“K1″) = w2: Range(“MIX!F1″) = i
Range(“F3:CQ6239″).Replace w1, w2, xlWhole
Next i
GoTo SHE
‘———————————————————————————
SHZ: If s = 3 Then Stop
Range(“X3:X” & d).ClearContents
Range(“ARC!B3:D6239″).ClearContents
Range(“ARC!B3:B6239″).ClearComments
If c = 7 Then Range(“ARC!E3:E6239″).Copy: Range(“ARC!B3″).PasteSpecial xlAll: _
Range(“ARC!F3:F6239″).FormulaR1C1 = “=LEN(RC[-4])”: _
Range(“ARC!G1″).FormulaR1C1 = “=SUM(R[2]C[-1]:R[6240]C[-1])”: _
Range(“X3:X” & d).ClearContents
Range(“ARC!A1″) = 2
zha:
ws = Range(“AB” & r) & ” ” & Range(“AA” & r) & ” ” & Range(“Z” & r)
Range(“ARC!B1″) = Cells(2, c) & “> ” & ws & “> “
w = Range(“C” & r)
Sheets(“ARC”).Select
ctr = Range(“G1″)
Range(“A3″).Select
Range(“C1″) = w
w1 = ” ” & w & ” “
If c = 7 And Range(“MIX!C” & r) Range(“MIX!C” & r – 1) Then _
Range(“B3:B6239″).Replace w1, ” ” & Chr(10) & w1, xlPart: _
ctr = ctr – Range(“G1″)
If c 7 Then Application.Run “ARC.ARC”
Sheets(“MIX”).Select
If c = 7 Then Range(“X” & r) = ctr / 2 Else Range(“X” & r) = Range(“ARC!E1″)
If ActiveCell.Offset(1, 0) = ActiveCell Then ActiveCell.Offset(1, 0).Select: _
r = ActiveCell.Row: GoTo zha
Sheets(“ARC”).Select: Range(“A1″) = 1
If c = 7 Then Range(“B3:B6239″).WrapText = False: _
Range(“F3:F6239″).ClearContents
GoTo SHE
‘———————————————————————————
SHE: Application.ScreenUpdating = True: Range(n & “D” & p) = Timer – t: End Sub
Dikirim oleh Pak Din 31 
