HeyItsTyler
New Member
- Joined
- Dec 12, 2018
- Messages
- 3
I'm working on constructing a macro that will look up information for a studio based on the studio identifier that is inputted. I am trying to set it up with error messages for the user that if the studio number is not found in the lookup area, it will return an error saying so and an additional error if the field is left blank. I am receiving an error that there is an Invalid Use of Property, highlighting in yellow the first line, "Sub studioinfolookup()". I'm very new to VBA so any direction and help would be greatly appreciated.
Code:
Sub studioinfolookup()
Dim studionumber As String
Dim address As String
Dim city As String
Dim state As String
Dim zip As Integer
studionumber = Range("C2")
Set addressrange = Range("L:M")
Set cityrange = Range("L:N")
Set staterange = Range("L:O")
Set ziprange = Range("L:P")
address = Application.WorksheetFunction.VLookup(studionumber, addressrange, 2, False)
city = Application.WorksheetFunction.VLookup(studionumber, cityrange, 3, False)
state = Application.WorksheetFunction.VLookup(studionumber, staterange, 4, False)
zip = Application.WorksheetFunction.VLookup(studionumber, ziprange, 5, False)
On Error GoTo myerrorhandler:
If Len(studionumber) > 0 Then
Set Range("B4:B7") = Nothing
Set Range("B4") = address
Set Range("B5") = city
Set Range("B6") = state
Set Range("B7") = zip
Else
MsgBox "No Studio Number Entered!"
End If
Exit Sub
myerrorhandler:
If Err.Number = 1004 Then
MsgBox "Studio Number not found. Please enter as (Country Code)(Store Identifier Number). Ex: CAN100 or US001"
End If
