Vlookup errors

[alymcmorland]

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,FALSE)"
Range("C29").Formula = "=VLOOKUP("Stringd",Hidden!$A$2:$K$134,11,FALSE)"
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

 

[Niek Otten]

For quotes within quotes, use two quotes

--
Kind regards,

Niek Otten

"alymcmorland" <[email protected]>
wrote in message
news:[email protected]...

 

[alymcmorland]

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

 

[Niek Otten]

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

--
Kind regards,

Niek Otten

"alymcmorland" <[email protected]>
wrote in message
news:[email protected]...

 

[alymcmorland]

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

 

[Niek Otten]

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