Vlookup nesting in VBA code

[dingo.xrull]

Can anyone tell me how to wrap this formula in VBA code so it runs as
a value?
I'm trying to understand how to nest this formula by using VBA codes.
I can do a basic Vlookup, but when it comes to dealing with the
errors, it is a struggle
=IF(ISERROR(VLOOKUP(IF(C3="",VLOOKUP(D3,Org!B:O,F3+2,0),IF
(D3="",VLOOKUP(C3,Org!B:O,F3+2,0))),$O$3:$O
$114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF(C3="",VLOOKUP(D3,Org!
B:O,F3+2,0),IF(D3="",VLOOKUP(C3,Org!B:O,F3+2,0))),$O$3:$O$114,1,0)))
I tried using this method
Sub Run_Alex()

With Sheets("Alex_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 & ",Data1!B:O,F3+2,0),IF(D" & _
i & "="""",VLOOKUP(C" & i & ",Data1!B:O,F3+2,0))),$O$3:$O
$114,1,0)),H" & i & "& ""C0MISCELLANEOUS"",H" & i & "& _(VLOOKUP(IF(C"
& "="""",VLOOKUP(D" & i & ",Data1!B:O,F3+2,0),IF(D" & "="""",VLOOKUP
(C" & i & ",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
Next i
End With
End Sub

But it keeps returning #Value.
If it is not too much trouble, could you step me through the logic?
It is giving me sleepless nights.
Thanks,
Xrull

 

[Bob Phillips]

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

With Sheets("Alex_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 & ",Data1!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" &
_
"C" & i & ",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i &
"& ""C0MISCELLANEOUS"",H" & i & _
"&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i &
",Data1!B:O,F3+2,0),IF(" & _
"D" & i & "="""",VLOOKUP(C" & i &
",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
Next i
End With
End Sub

 

[dingo.xrull]

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

    With Sheets("Alex_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 & ",Data1!B:O,F3+2,0),IF(D" & i &"="""",VLOOKUP(" &
_
                "C" & i & ",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i &
"& ""C0MISCELLANEOUS"",H" & i & _
                "&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i &
",Data1!B:O,F3+2,0),IF(" & _
                "D" & i & "="""",VLOOKUP(C" & i &
",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
        Next i
    End With
End Sub

--
__________________________________
HTH

Bob

Is it possible for me to use "Value" instead of "Formula" in the code?
I ask this because the formulastake a year and a day to calculate
before it gives me a result.
Thanks

 

[Dave Peterson]

Since you're using Evaluate, you're actually using the value returned by the
expression.

You may want to try putting the =vlookup() formulas in the cells, calculating
and converting to values to see if that is somewhat faster.

 

[Bob Phillips]

Sorry, that should be .Value, I had used .Formula in my testing to find the
problem and didn't re-instate it properly.

--
__________________________________
HTH

Bob

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

With Sheets("Alex_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 & ",Data1!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" &
_
"C" & i & ",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i &
"& ""C0MISCELLANEOUS"",H" & i & _
"&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i &
",Data1!B:O,F3+2,0),IF(" & _
"D" & i & "="""",VLOOKUP(C" & i &
",Data1!B:O,F3+2,0))),$O$3:$O$114,1,0)))")
Next i
End With
End Sub

--
__________________________________
HTH

Bob

Is it possible for me to use "Value" instead of "Formula" in the code?
I ask this because the formulastake a year and a day to calculate
before it gives me a result.
Thanks

 

[dingo.xrull]

Sorry, that should be .Value, I had used .Formula in my testing to find the
problem and didn't re-instate it properly.

--
__________________________________
HTH

Bob





Is it possible for me to use "Value" instead of "Formula" in the code?
I ask this because the formulastake a year and a day to calculate
before it gives me a result.
Thanks

I didn't delete the formulas from the sheet, and that is what was
causing the sheet to take a long time to calculate. I changed
"Formula" to "Value" but the formula gave me and error # Value! from
line 100. I don't know what to do to de-bug it.
Help me again please?
Dingo

 

[dingo.xrull]

I didn't delete the formulas from the sheet, and that is what was
causing the sheet to take a long time to calculate. I changed
"Formula" to "Value" but the formula gave me and error # Value! from
line 100. I don't know what to do to de-bug it.
Help me again please?Dingo

I mean the formulas started to show #Value! at row 100