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

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

"obc1126" wrote in message
...
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

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

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



"Dave Peterson" wrote:

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

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.


obc1126 wrote:

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

"Dave Peterson" wrote:

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


--

Dave Peterson