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 = _
Worksheets("Stores").Range("b3:f5")
Set shtComputers = Application.Workbooks("Credit.xls").Worksheets
("stores")
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"
Else
MsgBox "Value returned is " & strStore & strManager
End If
strLocation = _
Application.HLookup(5, _
Range("b3:f5"), 2, False)
strAnswer = _
MsgBox(strLocation & vbCrLf & strManager)
End Sub