Inputbox and Formulas

bahachin · February 15th, 2005, 12:59 PM

Hi, to all Gurus!
              I wrote the following codes with Inputbox to search from a table , combining it with a vlookup formula.
            I want the answer to be displayed on Range("b2") of the active worksheet.
    CODEs:
   Dim Num as Integer
   Num = Inputbox ("Please enter the search number")
   Worksheets("sheet1).range("B2").formula = "=vlookup(Num, Range(K2:M6),2)

     Each time I type in the number in the Inputbox to look up the resultant figure, Range("B2") displays "#Name?"

     Why is this so? I am using MS Office 2000 (version) .
     Can anybody help?
     Appreciate your kind assistance.

[u]</u>

Birger · February 15th, 2005, 02:36 PM

Try this:

"=vlookup(2, K2:M6,2)"

oops! This should do it:

Formula = "=vlookup(" & Num & ", K2:M6,2)"

gcianfanelli · February 15th, 2005, 03:17 PM

You're not appending the "Num" variable correctly to your function string. You are retrieving a value from your InputBox correctly, but when you subsequently build your function string you don't append, so Excel will assume that "Num" is a named range (which obviously doesn't exist.

Try:

Code:

  Dim Num as Integer
  Num = Inputbox ("Please enter the search number")
  Worksheets("sheet1").range("B2").formula = "=vlookup(" & Num & ", Range(K2:M6),2)"

bahachin · February 17th, 2005, 01:39 AM

Thank you for both your excellent help. I don't get that many good books here to guide me on such a simple approach. Your explanations clear up a misunderstood information on the use of operator " & ". It has to be spaced before and after
"Num".