VBA coding a nested Vlookup, and a sumif formula

X

Xrull

I need help.

I have this formula:

=IF(ISERROR(VLOOKUP(IF(C3="",VLOOKUP(D3,Data!B:O,F3+2,0),IF(D3="",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF(C3="",VLOOKUP(D3,Data!B:O,F3+2,0),IF(D3="",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))

And I tried using this code to run instead of the formula above because the
formula takes about 10 minute to run for worksheets, but it gives me #Value!
at row 100.

Sub Run_Data()
Dim iLastRow As Long
Dim i As Long

With Sheets("FBL3N_1")
iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To iLastRow 'iLastRow to 1 Step -1
.Cells(i, "K").Formula = Evaluate("=IF(ISERROR(VLOOKUP(IF(C" & i
& "="""",VLOOKUP(" & _
"D" & i & ",Data!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" &
"C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i & "&
""C0MISCELLANEOUS"",H" & i & _
"&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i &
",Data!B:O,F3+2,0),IF(" & "D" & i & "="""",VLOOKUP(C" & i &
",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
Next i
End With
End Sub


And how would I write a VBA Code to run from F7 to DN71 for the formula below:
I don’t want the formulas in the cell. I just want values. Formulas take too
long.

=SUMIF(LZL3N_1!$K$3:$K$43691,Summary!$A7&Summary!F$1,LZL3N_1!$I$3:$I$43691)+SUMIF(LZL3N_2!$K$3:$K$65536,Summary!$A7&Summary!F$1,LZL3N_2!$I$3:$I$65536)

Thanks,
Xrull
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top