I don't understand the looping code's formula, but...

It looks like you want to put the value directly into the cell. This may get
you started:

dim res as variant 'could be an error
dim LookUpRng as range
dim myVal as variant
dim myLookUpVal as variant

myval = activesheet.range("B2")

if myval = 9000000 then
set lookuprng = worksheets("Info").range("g2:H60")
mylookupval = activesheet.range("c2").value
else
set lookuprng = worksheets("info").range("j2:k60")
mylookupval = activesheet.range("b2").value
end if

res = application.vlookup(mylookupval, lookuprng, 2, false)

if iserror(res) then
res = "Not Found!"
end if

activesheet.cells(1,1).value = res



obc1126 wrote:

I have a VLookup formula which works fine. I want to convert the formula into
macros so that i don't have to copy and paste the formula each time i have a
new data.

I get an compile error says Sub or function not defined when i run the code.
What does this mean?

The formula : -
IF($B2=9000000,(VLOOKUP($C2,Info!$G$2:$H$60,2,FALS E)),(VLOOKUP($B2,Info!$J$2:$K$60,2,FALSE)))

The macro : -
Sub AssignJobDescription()

For i = 2 To FinalRow

If Cells(i, 2).Value = "9000000" Then
Cells(i, 8).Value = (IsNA(VLookup(Cells(i, 3).Value, "Info!G2:H60", "2",
False)))
Else
Cells(i, 8).Value = (IsNA(VLookup(Cells(i, 2).Value, "Info!J2:K48", "2",
False)))

Next i

End If

End Sub

--

Dave Peterson