Range variable not working in match command

I cannot get my code to recognize the variable punchrange
There is a sheet called HPpunch
There is data in B5:V16 (dollar values)
I keep getting the error type mis match
I have tried changing quite a few then but to no avail.
The text box only displays the value there is no input by the user. I just needed somewere to display the final value

Can sombody helpm with this

Thanks

Jamie Lill

Private Sub TextBox160_Change()
Dim PunchRange As Range
Dim Cnumber As Integer
Set PunchRange = Sheets("HPpunch").Range("B5:V16")
If ListBox11.Text = "HP" Then
Cnumber = "=Match(ListBox16.Value, PunchRange)"
TextBox160.Text = "=VLookup(ListBox13, PunchRange, Cnumber,)"
End If
End Sub

Please re-read the forum rules, and then edit your post to add code tags and to have a descriptive title.

Thanks.

Thanks.

What exactly are you trying to do? You can't intersperse worksheet formulas with VBA as you are doing, and MATCH is only valid when the range is a single row or column.

Hello jamielill,

If I interpreted your post correctly, this version of the macro should do what you want. However, it doesn't include error checking which should be added.

Private Sub TextBox160_Change()

  Dim PunchRange As Range
  Dim Cnumber As Integer
  Dim FoundIt As Range
  
    Set PunchRange = Sheets("HPpunch").Range("B5:V16")
    
      If ListBox11.Text = "HP" Then
         Set FoundIt = PunchRange.Find(ListBox16, , xlValues, xlWhole, _
                                       xlByColumns, xlNext, False)
         If Not FoundIt Is Nothing Then
            Cnumber = FoundIt.Row - PunchRange.Row + 1
            TextBox160.Text = WorksheetFunction.VLookup(ListBox13, PunchRange, Cnumber, False)
         End If
      End If

End Sub