Vlookup nesting in VBA code

[dingo...@gmail.com]

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

--
__________________________________
HTH

Bob

<dingo...@gmail.com> wrote in message
news:0a09095d-269a-4087...@v13g2000vbb.googlegroups.com...

[dingo...@gmail.com]

On Jan 14, 3:38 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> 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
>

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

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.

dingo...@gmail.com wrote:
>
<<snipped>>

> 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

[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

<dingo...@gmail.com> wrote in message
news:2cacbade-6aa5-4594...@41g2000yqf.googlegroups.com...

[dingo...@gmail.com]

On Jan 14, 11:39 am, "Bob Phillips" <Bob...@somewhere.com> wrote:
> 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
>

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...@gmail.com]

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