VLookup error - Sub or function not defined

  • Thread starter Thread starter obc1126
  • Start date Start date
O

obc1126

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,FALSE)),(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
 
Hi,

You can't put an Excel spreadsheet formula into VBA without making
adjustments. To get the idea, enter the formula manually in the
spreadsheet, put your cursor on the cell with the formula, turn the macro
recorder on and press F2, Enter. Then turn the recorder off and take a look
at the code.

Cheers,
Shane
 
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
 
what do we need to do this? what is a variant & range? Sorry if i ask a
stupid question as I'm just a newbie on excel VBA code.
dim res as variant 'could be an error
dim LookUpRng as range
dim myVal as variant
dim myLookUpVal as variant
Click to expand...
 
Variant is a data type that can hold anything--strings, numbers, errors,
arrays...

Range is a cell or a group of cells on a worksheet.

But I think it's time for a book to help with the basics.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Or maybe you can find some resources on line to help.
 
Back
Top