Vlookup errors
 

hi, im using the vlookup function


Code:
--------------------

=VLOOKUP("d",Hidden!$A$2:$K$134,1,FALSE)

--------------------


where this code says that it will look up d in the tab sheet hidden, in
the range A2 to K134, where 1 is the column value to return and false
makes sure it finds d.

Thats all well and gravy, but, i would like to write some code more
like this:


Code:
--------------------

Sub Macro_ChangeData()

Dim Stringd As String
Stringd = InputBox("Please enter an Employee's first initial followed by second name, e.g. 'A McMorland'")
Range("C9").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,1,FALSE )"
Range("C11").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,2,FALSE )"
Range("C13").Formula = "=VLOOKUP("Stringd ",Hidden!$A$2:$K$134,3,FALSE)"
Range("C15").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,4,FALSE )"
Range("C17").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,5,FALSE )"
Range("C19").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,6,FALSE )"
Range("C21").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,7,FALSE )"
Range("C23").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,8,FALSE )"
Range("C25").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,9,FALSE )"
Range("C27").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,10,FALS E)"
Range("C29").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,11,FALS E)"
End If

End Sub

--------------------


The only thing wrong with this code is that the second set of quotation
marks, around "Stringd" returns the error: Unexpected End of Statement

What i want it to do is when the user puts a name into a text box that
stringd becomes that name and then when that happens all the formulas
to the cells i have put above to be equal to the text that follows
them.

Can anyone see any errors as to why this doesn't work?
Also can anyone provide the correct code?

Thanks


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=484790

Vlookup errors
 

For quotes within quotes, use two quotes

--
Kind regards,

Niek Otten

"alymcmorland"
wrote in message
news:alymcmorland.1yhfbm_1131963902.2506@excelforu m-nospam.com...

hi, im using the vlookup function


Code:
--------------------

=VLOOKUP("d",Hidden!$A$2:$K$134,1,FALSE)

--------------------


where this code says that it will look up d in the tab sheet hidden, in
the range A2 to K134, where 1 is the column value to return and false
makes sure it finds d.

Thats all well and gravy, but, i would like to write some code more
like this:


Code:
--------------------

Sub Macro_ChangeData()

Dim Stringd As String
Stringd = InputBox("Please enter an Employee's first initial followed by
second name, e.g. 'A McMorland'")
Range("C9").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,1,FALSE )"
Range("C11").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,2,FALSE )"
Range("C13").Formula = "=VLOOKUP("Stringd ",Hidden!$A$2:$K$134,3,FALSE)"
Range("C15").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,4,FALSE )"
Range("C17").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,5,FALSE )"
Range("C19").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,6,FALSE )"
Range("C21").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,7,FALSE )"
Range("C23").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,8,FALSE )"
Range("C25").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,9,FALSE )"
Range("C27").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,10,FALS E)"
Range("C29").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,11,FALS E)"
End If

End Sub

--------------------


The only thing wrong with this code is that the second set of quotation
marks, around "Stringd" returns the error: Unexpected End of Statement

What i want it to do is when the user puts a name into a text box that
stringd becomes that name and then when that happens all the formulas
to the cells i have put above to be equal to the text that follows
them.

Can anyone see any errors as to why this doesn't work?
Also can anyone provide the correct code?

Thanks


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=484790

Vlookup errors
 

Ok i changed all the quotes within quotes to double quotes, so now th
code seems fine but, when i try put a name in all the cells just retur
the value "#N/A" and the error says value not available.

Does anyone have any idea as to why this might be happening

--
alymcmorlan
-----------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...fo&userid=2765
View this thread: http://www.excelforum.com/showthread.php?threadid=48479

Vlookup errors
 

Range("C9").Formula = "=VLOOKUP(""" & Stringd &
""",Hidden!$A$2:$K$134,1,FALSE)"

--
Kind regards,

Niek Otten

"alymcmorland"
wrote in message
news:alymcmorland.1yhg8z_1131965103.3351@excelforu m-nospam.com...

Ok i changed all the quotes within quotes to double quotes, so now the
code seems fine but, when i try put a name in all the cells just return
the value "#N/A" and the error says value not available.

Does anyone have any idea as to why this might be happening?


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=484790

Vlookup errors
 

thank you very much, that works perfectly, so what does the & and
quotation marks declare to excel?


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=484790

Vlookup errors
 

The & concatenate three parts of the formula to one string.
First part: Range("C9").Formula = "=VLOOKUP("
Second part: Stringd
Third part: ",Hidden!$A$2:$K$134,1,FALSE)"

--
Kind regards,

Niek Otten


"alymcmorland"
wrote in message
news:alymcmorland.1yhhva_1131967202.8109@excelforu m-nospam.com...

thank you very much, that works perfectly, so what does the & and
quotation marks declare to excel?


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=484790