VBA coding a nested Vlookup, and a sumif formula

D

dingo.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)

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

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



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
 
N

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,
 
B

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

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
 
D

dingo.xrull

Bob said:
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

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
Click to expand...
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
 
D

dingo.xrull

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
Click to expand...

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.
 

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

Similar Threads

Vlookup nesting in VBA code 6
VBA coding a nested Vlookup, and a sumif formula 1
vba sumif and vlookup codes 1
VLOOKUP 1
Vlookup N/A# error and VALUE# error 4
How to max out results of a formula. 6
Excel Vlookup and sumif function maybe a macro issue? 0
Excel formula for inventory problem 2

Top