Help with a VBA Loop with Nested If statement

[Spotycus]

Hello,

I have a very large excel sheet that uses a lot of VLookups Nested inside of If Statements and the calculation speed is horrible. Can someone help provide me a better way via VBA to accomplish the same goal? I recorded a Macro and added the formulas that are currently in the excel sheet.

The range that I need to make this loop through is ($F$4:$H500) or the named range: "Payee2MemoLookup".

The number of rows needed will vary from use to use based on the values in the "Payee_Subclass" Range

Sub Lookup_Macro()
'
' Lookup_Macro Macro
'
    Range("X1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,3,FALSE))"
        Range("G4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,4,FALSE))"
        Range("H4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,5,FALSE))"
        Range("F5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,3,FALSE))"
        Range("G5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,4,FALSE))"
        Range("H5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,5,FALSE))"
        Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,3,FALSE))"
        Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,4,FALSE))"
        Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(Payee_Subclass),"""",VLOOKUP([@SubClass],RulesLookup,5,FALSE))"
    Calculate
End Sub

Any help is greatly appreciated!!

 

[oldbrewer]

in this macro F4,G4,H4,F5,G5,H5 etc etc are selected

for j=6 to 8
for k=4 to 500
destinationcell=lookup(cells(j,k),tablename,colnum,false)
next k
next j

does this give you any ideas - I am unclear what you are actually doing....

 

[Spotycus]

Thanks for the help

The formulas above are repeating V lookups if they first satisfy an if statement. I have attached a link to a copy of the sheet if it will help make more sense. By typing in a value that matches from the list that is being looked up, the next three columns will auto populate based on the row that the answer matches. It would be ok to immediate copy the answers from the same row to the different columns, but each row will match to a different row in the lookup table. I am using the lookup word as a method to assign an accounting category, class, and specialized memo. I left some dummy data in the file to help it make better sense.

07.21.2017 Excelchat transaction Worksheet.xlsm - Google Drive

Thanks again!