"object required" error vlookup (1 Viewer)

Local time
Today, 11:11
Joined
Mar 13, 2021
Messages
354
i getting this error for no reason. my return variable a variant like microsoft says to do here

this is all my code on combo box. and my file attached. what am i doing wrong? thank you.

Code:
Private Sub Choose_Change()
Dim tChoice As String
Dim numPos As Long
Dim endResponse As Long
Dim lenResponse As Long
Dim nChoice As String
Dim iName As String
Dim rMessage As Variant
Dim sMessage As String
Const errMessage1 As String = "No name was entered.  Please try again."
Const response As String = "RESPONSE"
Const space As String = " "

tChoice = cmbChoose.Value
lenResponse = Len(response)
numPos = InStr(lenResponse, tChoice, " ") + 1
nChoice = Mid(tChoice, numPos, 1)
  If nChoice = "3" Then
InputName:
    iName = InputBox("Please enter the name of who you want to send the template to...", "Input")
      If iName = "" Then
        MsgBox errMessage1, vbCritical, "Error"
        GoTo InputName
      End If
  End If
    rMessage = WorksheetFunction.VLookup(tChoice, responses.Range("a:b"), 2, False)
      If Not IsError(rMessage) Then
        sMessage = CStr(rMessage)
        sMessage = Replace(sMessage, "[name]", StrConv(iName, vbProperCase))
        main.preview = sMessage
      Else
        GoTo err
      End If
        Exit Sub

err:
  MsgBox "An error occurred during the routine.  Details are as follows:" & Chr(10) & Chr(10) & _
         "Error Number: " & err.Number & Chr(10) & _
         "Description: " & err.Description, vbCritical, "Error"
End Sub
 

Attachments

  • forum.zip
    21.1 KB · Views: 3

moke123

AWF VIP
Local time
Today, 04:11
Joined
Jan 11, 2013
Messages
2,413
I'm not an excel person but I did notice one thing.

you had "tChoice = cmbchoose.Value" however your combobox was named "choose"
When I changed that line you then get a different error on this line
rMessage = WorksheetFunction.VLookup(tChoice, responses.Range("a:b"), 2, False)
it highlights "responses" as invalid qualifier. Should that be response?
 

arnelgp

error reading drive A:
Local time
Today, 16:11
Joined
May 7, 2009
Messages
12,333
try and test.
 

Attachments

  • forum.zip
    21.4 KB · Views: 2
Local time
Today, 11:11
Joined
Mar 13, 2021
Messages
354
I'm not an excel person but I did notice one thing.

you had "tChoice = cmbchoose.Value" however your combobox was named "choose"
When I changed that line you then get a different error on this line

it highlights "responses" as invalid qualifier. Should that be response?
4oh wait! responses is sheet name. i forgot to make sheets collection as qualifier! oops. thank you.

by the way how did you know that responses was invalid qualifier? it just highlight line for me. it does say what is offend word. how did you get that?
 
Local time
Today, 11:11
Joined
Mar 13, 2021
Messages
354
i got it. it is fixed! i also had issue with not seeing all text in textbox. also seeing paragraph mark symbols instead of line break like i wanted. here is my code that is changed

Code:
    rMessage = WorksheetFunction.VLookup(tChoice, Sheets("responses").Range("a:b"), 2, False)
      If Not IsError(rMessage) Then
        sMessage = CStr(rMessage)
        sMessage = Replace(sMessage, "[name]", StrConv(iName, vbProperCase))
        main.txtPreview = sMessage
      Else
        GoTo err
      End If
        Exit Sub

and this is what output looks like. thank you! :)

resultfix.jpg
 

Users who are viewing this thread

Top Bottom