vlookup vba question.

N

Nigel

Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into cell E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
 
A

Arvi Laanemets

Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets
 
N

Nigel

Hi, thanks for replying. the only problem is where the variable which equals
"November 2005" is, has to change every month according to the month of year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it so
i can insert the file name as per month.



thanks,


Nigel



Arvi Laanemets said:
Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Nigel said:
Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into cell E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
 
A

Arvi Laanemets

Hi

.....
CurrMonth=Format(Date,"mmmm yyyy")
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[" & CurrMonth & ".xls]'Front Order
Summary!E10:F30,2)"
.....


Arvi Laanemets


Nigel said:
Hi, thanks for replying. the only problem is where the variable which equals
"November 2005" is, has to change every month according to the month of year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it so
i can insert the file name as per month.



thanks,


Nigel



Arvi Laanemets said:
Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Nigel said:
Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into
Click to expand...
cell
E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
Click to expand...
 
N

Nigel

Hi Arvi,

the text you had written did not seem to work though i did change the values
to the correct ones. i have copied my complete macro below to view. when
checking themn the variables are all collecting the right information, i am
not getting an error but the formula is not getting entered into the cell.
Infact, it is not entering anywhere. If you don't mind, could you look at it
and see where i am going wrong please as its confusing me...

Sub GETWIP()

CurrMonth = Range("C15").Text

Range("E15").Select
ActiveCell.FormulaR1C1 = togetvalue
thepath = "=vlookup(C2,C:\Documents and Settings\Nigel Shaw\My Documents\'["
& CurrMonth & ".xls]'Front Order Summary!H12:I36,2)"
getjobcode = Range("C2").Value

If getjobcode = "Complete" Then
MsgBox " you need to enter a job number first and fill the book details
correctly! "
Exit Sub
End If

If getjobcode <> "Complete" Then

togetvalue = thepath
Range("C1").Select
ActiveCell.Select
End If


Exit Sub

End Sub

thanks again,

Nigel

Arvi Laanemets said:
Hi

.....
CurrMonth=Format(Date,"mmmm yyyy")
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[" & CurrMonth & ".xls]'Front Order
Summary!E10:F30,2)"
.....


Arvi Laanemets


Nigel said:
Hi, thanks for replying. the only problem is where the variable which equals
"November 2005" is, has to change every month according to the month of year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it so
i can insert the file name as per month.



thanks,


Nigel



Arvi Laanemets said:
Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into cell
E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
Click to expand...
 
P

Pete

It looks as if togetvalue on the 3rd line will be empty - you haven't
defined it at this point.

Also, why not use If ... Then ... Else ... Endif rather than the two
If's?

I suggest you move your second and third line to after the second End
If, and get rid of the second Exit Sub.

Pete
 
D

Dave Peterson

Maybe a shorter version will help you debug it.

Option Explicit
Sub GETWIP()

dim CurrMonth as string
dim ToGetValue as string

CurrMonth = Range("C15").Text

ToGetValue _
= "=vlookup(C2,'C:\Documents and Settings\Nigel Shaw\My Documents\[" _
& CurrMonth & ".xls]Front Order Summary'!H12:I36,2)"

Range("E15").FormulaR1C1 = togetvalue

end Sub

And watch your placement of the apostrophes. That was part of the problem.
Hi Arvi,

the text you had written did not seem to work though i did change the values
to the correct ones. i have copied my complete macro below to view. when
checking themn the variables are all collecting the right information, i am
not getting an error but the formula is not getting entered into the cell.
Infact, it is not entering anywhere. If you don't mind, could you look at it
and see where i am going wrong please as its confusing me...

Sub GETWIP()

CurrMonth = Range("C15").Text

Range("E15").Select
ActiveCell.FormulaR1C1 = togetvalue
thepath = "=vlookup(C2,C:\Documents and Settings\Nigel Shaw\My Documents\'["
& CurrMonth & ".xls]'Front Order Summary!H12:I36,2)"
getjobcode = Range("C2").Value

If getjobcode = "Complete" Then
MsgBox " you need to enter a job number first and fill the book details
correctly! "
Exit Sub
End If

If getjobcode <> "Complete" Then

togetvalue = thepath
Range("C1").Select
ActiveCell.Select
End If

Exit Sub

End Sub

thanks again,

Nigel

Arvi Laanemets said:
Hi

.....
CurrMonth=Format(Date,"mmmm yyyy")
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[" & CurrMonth & ".xls]'Front Order
Summary!E10:F30,2)"
.....


Arvi Laanemets


Nigel said:
Hi, thanks for replying. the only problem is where the variable which equals
"November 2005" is, has to change every month according to the month of year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it so
i can insert the file name as per month.



thanks,


Nigel



:

Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into cell
E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
Click to expand...
 
N

Nigel

Dear Dave,

Thank you very much, this worked well and compiled the formual and entered
it into the correct place. however i have now come across another problem.

i have not really used vlookup before so i was wanting to ask a question
regarding the ranges used within the lookup.

my actual range is H12:I36 but not all of the cells contain values until
such a time where it needs to be filled in according to new job numbers. so
the vlookup seems to return the incorrect value. for example:

( say my C2 cell contains 1234 )
(H) (I)
1234 £568.44
6344 £7865.44
5877 £4766.99

my lookup value returned should be £568.44 which it is but if i change the
value of C2 to 5877, it still returns £568.44. if i change C2 to 6344 the
returned value is 4766.99

this is really confusing me. i can send over my 2 workbooks if you like so
you can see what i am actually trying to achieve.

Thanks in advance,


Nigel


Dave Peterson said:
Maybe a shorter version will help you debug it.

Option Explicit
Sub GETWIP()

dim CurrMonth as string
dim ToGetValue as string

CurrMonth = Range("C15").Text

ToGetValue _
= "=vlookup(C2,'C:\Documents and Settings\Nigel Shaw\My Documents\[" _
& CurrMonth & ".xls]Front Order Summary'!H12:I36,2)"

Range("E15").FormulaR1C1 = togetvalue

end Sub

And watch your placement of the apostrophes. That was part of the problem.
Hi Arvi,

the text you had written did not seem to work though i did change the values
to the correct ones. i have copied my complete macro below to view. when
checking themn the variables are all collecting the right information, i am
not getting an error but the formula is not getting entered into the cell.
Infact, it is not entering anywhere. If you don't mind, could you look at it
and see where i am going wrong please as its confusing me...

Sub GETWIP()

CurrMonth = Range("C15").Text

Range("E15").Select
ActiveCell.FormulaR1C1 = togetvalue
thepath = "=vlookup(C2,C:\Documents and Settings\Nigel Shaw\My Documents\'["
& CurrMonth & ".xls]'Front Order Summary!H12:I36,2)"
getjobcode = Range("C2").Value

If getjobcode = "Complete" Then
MsgBox " you need to enter a job number first and fill the book details
correctly! "
Exit Sub
End If

If getjobcode <> "Complete" Then

togetvalue = thepath
Range("C1").Select
ActiveCell.Select
End If

Exit Sub

End Sub

thanks again,

Nigel

Arvi Laanemets said:
Hi

.....
CurrMonth=Format(Date,"mmmm yyyy")
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[" & CurrMonth & ".xls]'Front Order
Summary!E10:F30,2)"
.....


Arvi Laanemets


Hi, thanks for replying. the only problem is where the variable which
equals
"November 2005" is, has to change every month according to the month of
year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it
so
i can insert the file name as per month.



thanks,


Nigel



:

Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into
cell
E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

First, do you want to find an exact match in column H.

If yes, then change your formula to look more like:

= "=vlookup(C2,'C:\Documents and Settings\Nigel Shaw\My Documents\[" _
& CurrMonth & ".xls]Front Order Summary'!H12:I36,2,False)"

That False at the end means that you want an exact match in the key column.

If you don't want an exact match, then you'll want to sort that table in H:I.

From Excel's help for =vlookup() (when you specify True or omit it):

if an exact match is not found, the next largest value that is less than
lookup_value is returned

And with 5877, excel skips by 1234, but notices that 6344 is already too big.
So it drops back to the value associated with 1234.


Dear Dave,

Thank you very much, this worked well and compiled the formual and entered
it into the correct place. however i have now come across another problem.

i have not really used vlookup before so i was wanting to ask a question
regarding the ranges used within the lookup.

my actual range is H12:I36 but not all of the cells contain values until
such a time where it needs to be filled in according to new job numbers. so
the vlookup seems to return the incorrect value. for example:

( say my C2 cell contains 1234 )
(H) (I)
1234 £568.44
6344 £7865.44
5877 £4766.99

my lookup value returned should be £568.44 which it is but if i change the
value of C2 to 5877, it still returns £568.44. if i change C2 to 6344 the
returned value is 4766.99

this is really confusing me. i can send over my 2 workbooks if you like so
you can see what i am actually trying to achieve.

Thanks in advance,

Nigel

Dave Peterson said:
Maybe a shorter version will help you debug it.

Option Explicit
Sub GETWIP()

dim CurrMonth as string
dim ToGetValue as string

CurrMonth = Range("C15").Text

ToGetValue _
= "=vlookup(C2,'C:\Documents and Settings\Nigel Shaw\My Documents\[" _
& CurrMonth & ".xls]Front Order Summary'!H12:I36,2)"

Range("E15").FormulaR1C1 = togetvalue

end Sub

And watch your placement of the apostrophes. That was part of the problem.
Hi Arvi,

the text you had written did not seem to work though i did change the values
to the correct ones. i have copied my complete macro below to view. when
checking themn the variables are all collecting the right information, i am
not getting an error but the formula is not getting entered into the cell.
Infact, it is not entering anywhere. If you don't mind, could you look at it
and see where i am going wrong please as its confusing me...

Sub GETWIP()

CurrMonth = Range("C15").Text

Range("E15").Select
ActiveCell.FormulaR1C1 = togetvalue
thepath = "=vlookup(C2,C:\Documents and Settings\Nigel Shaw\My Documents\'["
& CurrMonth & ".xls]'Front Order Summary!H12:I36,2)"
getjobcode = Range("C2").Value

If getjobcode = "Complete" Then
MsgBox " you need to enter a job number first and fill the book details
correctly! "
Exit Sub
End If

If getjobcode <> "Complete" Then

togetvalue = thepath
Range("C1").Select
ActiveCell.Select
End If

Exit Sub

End Sub

thanks again,

Nigel

:

Hi

.....
CurrMonth=Format(Date,"mmmm yyyy")
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[" & CurrMonth & ".xls]'Front Order
Summary!E10:F30,2)"
.....


Arvi Laanemets


Hi, thanks for replying. the only problem is where the variable which
equals
"November 2005" is, has to change every month according to the month of
year.
i have 12 books from jan to dec where i need the data from theses sheets
independently throughout the month in the same cell. i need to compile it
so
i can insert the file name as per month.



thanks,


Nigel



:

Hi

....
Sheets("Sheet1").Range("E15").Formula="=vlookup(C2,C:\Documents and
Settings\My Name\My Documents\'[November 2005.xls]'Front Order
Summary!E10:F30,2)"
....


Arvi Laanemets



Hi,

i was wondering if i could have some help? here are my variables:

getcell = Range("C2").Value
MyPath = "C:\Documents and Settings\My Name\My Documents\"
My File = Range("D15").Text & ".xls"

i need to create a macro to insert a compiled vlookup formula into
cell
E15
The finished formula should be something like:
=vlookup(C2,C:\Documents and Settings\My Name\My Documents\'[November
2005.xls]'Front Order Summary!E10:F30,2)

but i keep getting errors. does anyone have any ideas?

thanks in advance,

NS
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP & INDIRECT 2
Excel 2003 - VBA - Remote access 3
Embedding formula in a formula 6
Vlookup with an Arrayformula.... HELP! 0
Worksheet name as a variable in VBA 1
VLOOKUP returning #N/A result 2
VLookup or Macro? 0
#value! error 2

Top