MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA & Hlookup from a newbie

  Asked By: Rose    Date: Nov 26    Category: MS Office    Views: 3708

I am trying to write a VBA macro to use HLookup. The spreadsheet is a
simple one:
store # 1 2 3 4
location blah1 blah2 blah 3
mgr name name1 name2 name3

I use an input box to get the Store # and I want to use that to get
location & mgr name. strStore is the variable name. I cannot figure
out how to use it in HLookup. I tried using it this way (with & without
quotation marks)
strLocation = _
Application.HLookup(strStore, _
Range("b3:f5"), 2, False)
then i tried making an integer variable, intStore which I set equal to
Val("strStore"). Still doesn't work with or w/o quotation marks

strLocation = _
Application.HLookup(intStore, _
Range("b3:f5"), 2, False)

However, if I hard code a store number, it works perfectly. I have used
the immediate window and the values of intStore or strStore are corret.
I can print the values this way
strAnswer = _
' MsgBox(strLocation & vbCrLf & strManager)

My head is going bzz bzz. Please Help
Here is the whole deal

Option Explicit
Public Sub DisplayInfo()
Dim strStore As String
Dim strLocation As String
Dim strManager As String
Dim strAnswer As String
Dim intStore As Integer
Dim rngStoreInfo As Range
Dim shtComputers As Worksheet
Set rngStoreInfo = _
Set shtComputers = Application.Workbooks("Credit.xls").Worksheets
strStore = _
InputBox(Prompt:="Enter the store number", _
Title:="Get Store Number", Default:="1")
intStore = Val("strStore")
strManager = _
Application.HLookup(intStore, _
Range("b3:f5"), 3, False)
If IsError(strManager) Then
MsgBox "No match"
MsgBox "Value returned is " & strStore & strManager
End If

strLocation = _
Application.HLookup(5, _
Range("b3:f5"), 2, False)
strAnswer = _
MsgBox(strLocation & vbCrLf & strManager)

End Sub



4 Answers Found

Answer #1    Answered By: Barabas Cohen     Answered On: Nov 26

> intStore = Val("strStore")

is trying to extract a number  from the string literal "strStore".
There isn't one there, of course. It should be extracting it from
the contents of the variable  strStore:

> intStore = Val(strStore)

and your code  then works.

Note that your second Hlookup

> strLocation = _
> Application.HLookup(5, _
> Range("b3:f5"), 2, False)

actually fails in my test, because there was no store  5 in your data
example. The error is "Type Mismatch". The reason for this is that
it is trying to put an error code into strLocation, which is a
string variable.

If there is any possibility of the lookup returning an error code,
you will need to declare the destination variable as Variant.

Note that this will also be one of the reasons you were having
trouble with the direct use of strStore in the lookup. The other
reason, presumably, is that HLookUp won't match a string containing
a "1" with a cell containing a 1, for some reason.

Answer #2    Answered By: Stefan Thompson     Answered On: Nov 26

no wonder my forehead is flat!Thank you for your help; now I
can sleep at night.

For a newbie, or at least this one, sorting out the "", :=, =, () is
making me crazy.

PS application.hlookup works  & my VBA (Ms Office 2003) squawks if if
I use the application.workbookfunction.hlookup.

Answer #3    Answered By: Ivan Coleman     Answered On: Nov 26

I think you will need to use


Answer #4    Answered By: Jet Brown     Answered On: Nov 26

I'm using msOffice2003, and have always used
Application.WorkSheetFunction.(insert function here).
Keep in mind that Excel differentiates a workBOOK from a workSHEET.
there are some things that you do with a workBOOK that are not available to a
single workSHEET. For instance, you SAVE a workBOOK, that contains a collection
of SHEETS. At the same time, a workBOOK doesn't have cells, or ranges, or
fonts, or borders (etc.), those belong to a workSHEET.
So, you can't do a horizontal lookup on a workBOOK, you just do it to a

In SOME cases, Excel will make the proper selection for you.
In your case, if you leave it out, since HLookup only applies to sheets, excel
places that in your code  for you.

In my opinion, I suggest telling Excel what to use instead of letting Excel make
assumptions. It may be that the next revision won't be so kind, and you'll
spend yours debugging code to make it run in the new version...

Didn't find what you were looking for? Find more on VBA & Hlookup from a newbie Or get search suggestion and latest updates.