VBA coding a nested Vlookup, and a sumif formula

[dingo...@gmail.com]

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)

I posted the first part of the question, but it was partially solved.
Thanks,
Dingo

[Niek Otten]

Are you sure you want all rows of the columns B:O? Certainly with the 4th
argument to false, as you have, a non-present value will cause all those
rows to be searched.
Is the table sorted? Then you can speed things up enormously by using a 4th
argument TRUE and check for the presence yourself. I have seen cases where
several minutes search time were reduced to less than a second.
In your case, you would write

=if(vlookup(D3,Data!B1:B250,1)=D3,VLOOKUP(D3,Data!B1:O250,F3+2,0),NA())

Adapt 250 to what you need.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

<dingo...@gmail.com> wrote in message
news:87a9341e-499f-44c4...@r13g2000vbp.googlegroups.com...

[Niek Otten]

<=if(vlookup(D3,Data!B1:B250,1)=D3,VLOOKUP(D3,Data!B1:O250,F3+2,0),NA())>

should have been

=if(vlookup(D3,Data!B1:B250,1)=D3,VLOOKUP(D3,Data!B1:O250,F3+2),NA())

Sorry,

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" <nico...@xs4all.nl> wrote in message
news:CA725430-98A1-4142...@microsoft.com...

[Bob Phillips]

It works fine for me, after tidying up the NG word-wrap mess.

Try this version.

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").Value = 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

--
__________________________________
HTH

Bob

<dingo...@gmail.com> wrote in message
news:87a9341e-499f-44c4...@r13g2000vbp.googlegroups.com...

[dingo...@gmail.com]

The code that Bob suggested still gives me a value at row 100.
I haven't tried the other suggestion. I'm going to try it in a few
hours

[dingo...@gmail.com]

> > <dingo.xr...@gmail.com> wrote in message

Sorry, I didn't know I couldn't cross post. I just needed an answer
like yesterday. I've posted in about 5 different forums. I thought I'd
get an answer fast.
Now that is why I couldn't veiw the response in another post. I
thought my computer was messing up or something.