vlookup vba question.

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

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

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.

Nigel wrote:
>
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

--

Dave Peterson

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" wrote:

> 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.
>
> Nigel wrote:
> >
> > 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" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
> --
>
> 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.



Nigel wrote:
>
> 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" wrote:
>
> > 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.
> >
> > Nigel wrote:
> > >
> > > 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" wrote:
> > >
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" wrote:
> > > > >
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson