Gaurangaero
New Member
Hello,
I have excel sheet with complex formula, I want a VBA code which can generat the same information.
CODE is below
Problem is by using following code I can add formula to the cell but then my file size is too heavy can anybody suggest some thing to make faster and lighter excel file??
Gaurang
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
Sub Cost_compare_final()
'
' Macro1 Macro
'
Dim j As String
Dim currentsht As String
Dim crtsht As Worksheet
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Do Until WorksheetExists(currentsht)
currentsht = InputBox("Enter current week sheet name")
If Not WorksheetExists(currentsht) Then MsgBox currentsht & " doesn't exist!", vbExclamation
Loop
Do Until WorksheetExists(j)
j = InputBox("Enter Worksheet name to compare")
If Not WorksheetExists(j) Then MsgBox j & " doesn't exist!", vbExclamation
Loop
Set crtsht = Sheets(currentsht)
crtsht.Select
Range("GL15:GL" & LastRow).Formula = _
"=IFERROR(IF(AND(INDEX(" & j & "!R:R,MATCH(H15," & j & "!H:H,0))<>""Rejected"",R15=""Rejected""),""Test Rejected"",""Test Maintained""),""Test Added"")"
Range("GM15:GM" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0))),IFERROR(EB15-INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0)),IF(EB15<>"""",EB15,0))),0)"
Range("GN15:GN" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GM15 <>0,IF(GM15 >0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GO15:GO" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0))),IFERROR(EC15-INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0)),IF(EC15<>"""",EC15,0))),0)"
Range("GP15:GP" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GO15<>0,IF(GO15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GQ15:GQ" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0))),IFERROR(ED15-INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0)),IF(ED15<>"""",ED15,0))),0)"
Range("GR15:GR" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GQ15<>0,IF(GQ15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GS15:GS" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0))),IFERROR(EE15-INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0)),IF(EE15<>"""",EE15,0))),0)"
Range("GT15:GT" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GS15<>0,IF(GS15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GU15:GU" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0))),IFERROR(EF15-INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0)),IF(EF15<>"""",EF15,0))),0)"
Range("GV15:GV" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GU15<>0,IF(GU15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GW15:GW" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0))),IFERROR(EG15-INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0)),IF(EG15<>"""",EG15,0))),0)"
Range("GX15:GX" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GW15<>0,IF(GW15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GY15:GY" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0))),IFERROR(EH15-INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0)),IF(EH15<>"""",EH15,0))),0)"
Range("GZ15:GZ" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GY15<>0,IF(GY15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
End Sub
I have excel sheet with complex formula, I want a VBA code which can generat the same information.
CODE is below
Problem is by using following code I can add formula to the cell but then my file size is too heavy can anybody suggest some thing to make faster and lighter excel file??
Gaurang
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
Sub Cost_compare_final()
'
' Macro1 Macro
'
Dim j As String
Dim currentsht As String
Dim crtsht As Worksheet
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Do Until WorksheetExists(currentsht)
currentsht = InputBox("Enter current week sheet name")
If Not WorksheetExists(currentsht) Then MsgBox currentsht & " doesn't exist!", vbExclamation
Loop
Do Until WorksheetExists(j)
j = InputBox("Enter Worksheet name to compare")
If Not WorksheetExists(j) Then MsgBox j & " doesn't exist!", vbExclamation
Loop
Set crtsht = Sheets(currentsht)
crtsht.Select
Range("GL15:GL" & LastRow).Formula = _
"=IFERROR(IF(AND(INDEX(" & j & "!R:R,MATCH(H15," & j & "!H:H,0))<>""Rejected"",R15=""Rejected""),""Test Rejected"",""Test Maintained""),""Test Added"")"
Range("GM15:GM" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0))),IFERROR(EB15-INDEX(" & j & "!EB:EB,MATCH(H15," & j & "!H:H,0)),IF(EB15<>"""",EB15,0))),0)"
Range("GN15:GN" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GM15 <>0,IF(GM15 >0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GO15:GO" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0))),IFERROR(EC15-INDEX(" & j & "!EC:EC,MATCH(H15," & j & "!H:H,0)),IF(EC15<>"""",EC15,0))),0)"
Range("GP15:GP" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GO15<>0,IF(GO15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GQ15:GQ" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0))),IFERROR(ED15-INDEX(" & j & "!ED:ED,MATCH(H15," & j & "!H:H,0)),IF(ED15<>"""",ED15,0))),0)"
Range("GR15:GR" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GQ15<>0,IF(GQ15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GS15:GS" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0))),IFERROR(EE15-INDEX(" & j & "!EE:EE,MATCH(H15," & j & "!H:H,0)),IF(EE15<>"""",EE15,0))),0)"
Range("GT15:GT" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GS15<>0,IF(GS15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GU15:GU" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0))),IFERROR(EF15-INDEX(" & j & "!EF:EF,MATCH(H15," & j & "!H:H,0)),IF(EF15<>"""",EF15,0))),0)"
Range("GV15:GV" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GU15<>0,IF(GU15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GW15:GW" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0))),IFERROR(EG15-INDEX(" & j & "!EG:EG,MATCH(H15," & j & "!H:H,0)),IF(EG15<>"""",EG15,0))),0)"
Range("GX15:GX" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GW15<>0,IF(GW15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
Range("GY15:GY" & LastRow).Formula = _
"=IFERROR(IF(GL15=""Test Rejected"",IF(INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0))="""",0,-INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0))),IFERROR(EH15-INDEX(" & j & "!EH:EH,MATCH(H15," & j & "!H:H,0)),IF(EH15<>"""",EH15,0))),0)"
Range("GZ15:GZ" & LastRow).Formula = _
"=IF(GL15=""Test Rejected"",""Test Rejected"",IF(R15=""Rejected"", ""N/A"",IF(GL15=""Test Maintained"",IF(GY15<>0,IF(GY15>0,""Forecast increased"",""Forecast decreased""),""No change""),""New test"")))"
End Sub
