VBA to vlookup and loop through range

[vimal_uae]

HI frinds,

i need a VBA code to replace below vlookup formula in each rows in T column till the data ends. My formula (given) starts in T2.

=IF(V3>0,IF(P3<>"",Q3-1,T2),IF(P3<>"",IF(V3+VLOOKUP(Q3,Deal!D:F,3,0)>0,Q3,IF(V3+VLOOKUP(Q3,Deal!D:F,3,0)+VLOOKUP(Q3+1,Deal!D:F,3,0)>0,Q3+1,"no fund")),IF(V3+VLOOKUP(T2+1,Deal!D:F,3,0)>0,T2+1,IF(V3+VLOOKUP(T2+1,Deal!D:F,3,0)+VLOOKUP(T2+2,Deal!D:F,3,0)>0,T2+2,"no fund"))))


i need only the resulted value only to be updated in T2, that's y i need it VBA. I'm not an expert in VBA but i do some by using recording function.

it would be grateful if someone could help me.

Thankyu,
vimal

 

[Jubjab]

the formula is long and hard to interpret, so it could help if you would write in normal language, what it is expected to do. Like

If V3 > 0 then check if P3 is <> "". Otherwise end result is T2.
Else calculate if VLOOKUP(Q3,Deal!D:F,3,0)>0, if TRUE then ...

and so on

 

[vimal_uae]

Hi Jubjab ,


I Wrote this code with little knowledge. this worked correct for only one cell. think this will clarify. i need this thing to loop through all rows in column T till the data ends. Kindly help me...

If Range("V2") > 0 Then
If Range("P2") <> "" Then
Range("T2") = Range("Q2") - 1
Else
Range("T2") = Range("T1").Value
End If
Else
If Range("P2") <> "" Then
If Range("V2") + WorksheetFunction.VLookup(Range("Q2"), Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("Q2").Value
ElseIf Range("V2") + WorksheetFunction.VLookup(Range("Q2"), Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("Q2") + 1, Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("Q2") + 1
ElseIf Range("V2") + WorksheetFunction.VLookup(Range("Q2"), Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("Q2") + 1, Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("Q2") + 2, Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("Q2") + 2
Else
Range("T2") = "No Fund"
End If
Else
If Range("V2") + WorksheetFunction.VLookup(Range("T2") + 1, Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("T2") + 1
ElseIf Range("V2") + WorksheetFunction.VLookup(Range("T2") + 1, Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("T2") + 2, Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("T2") + 2
ElseIf Range("V2") + WorksheetFunction.VLookup(Range("T2") + 1, Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("T2") + 2, Sheets("Deal").Range("D:F"), 3, 0) + _
WorksheetFunction.VLookup(Range("T2") + 3, Sheets("Deal").Range("D:F"), 3, 0) > 0 Then
Range("T2") = Range("T2") + 3
Else
Range("T2") = "No Fund"
End If
End If
End If

 

[Jubjab]

If that code works, you can simply add a loop to have it work trough the other rows, like

for i = 2 to 1000
...your code here
next i

Then change all range definitions to include i instead of a fixed row, like

If Range("V2") --> If Range("V" & i)

 

[vimal_uae]

Hi Jubjab

its worked great! thanksss

one more thing.. how to loop it to end of data rather than to only 1000?. rows counts often varies...

 

[Jubjab]

use a code like

lastrow = range("A65536").end(xlup).row

and change 1000 to lastrow, i.e.

for i = 1 to lastrow