Offset Match function in VBA/Userform

Question

I'm trying to set up a userform where the value chosen in the combobox determines the range of an offset match function. The lookup value is entered within a textbox (TextBox1) in the same userform.

If ComboBox1 = "Something" Then
TryRun = Evaluate("Offset(Sheets('Sheet1').Range('B13'),MATCH(TextBox1.Value,Sheets('Sheet1').Range('B14:B33'),0),5)")
End If

Currently this returns the value of cell G15, what I need it to do however is to simply select that cell and paste the value of Textbox (TextBox2) in the userform.

This doesn't seem to work, Cells(TryRun).Value = TextBox2, because it cannot find a defined object for 'TryRun'. However, seeing as this refers to a cell, I wouldn't know what to define it as.

Could anyone help me out?

I've tried Ahmed's U Vlookup solution to get this:

Private Sub OkButton_Click()

If Combobox1 = "Something" Then
TryRun = WorksheetFunction.VLookup(Val(Textbox1.Text), Range("B14:G33"), 6, False)

    If IsEmpty(TryRun) Then
    Msgbox "nope"
    Textbox2.Text = "No Match found"
    Else
    Msgbox "almost..."
    TextBox2.Value = TryRun
    End If
   End If

End Sub

This results in Textbox2 returning value = 0 within the userform whilst nothing gets pasted through the vlookup function.

By removing the on error lines, the code stops working all together, with the faulty line being TryRun = WorksheetFunction.VLookup(Val(Textbox1.Text), Sheets("Stuff").Range("B14:G33"), 6, False). The error this results in is: Error 424, object required.

EDIT:

After removing Dim TryRun As Long I've come across the next stumbling block: Currently I'm returning "No Match Found" Instead of Try Run's value in the designated cell address.

EDIT 2:

Somehow, I'm returning the second messagebox now in the else statement. Still no value being pasted in TryRun though.

Start from something simple and try to build a minimal reproducible example. Hint - it does not matter that you work in a UserForm at all. Nor that your sheet is Sheet1, B13, TextBox1, etc. For a MCVE hard-code all these values to something easy to reproduce. Or look at the address formula and try to write it somewhere. Have fun! :) — Vityata
Evaluate is for running worksheet function formulas, not for VBA. — Tim Williams
Please comment out 'Dim TryRun As Long'., (This is to be used with WorkSheetFunction.Match only since it returns offset row count)..What is the textBox1.Text when OKButton Clicked? Does the number exists in B14:B33 range — Ahmed AU
Error 424 on line 'TryRun=…..' raises doubt about existence of Object TextBox1 on the Userform. Try "MsgBox TextBox1.Text" before 'TryRun=….' — Ahmed AU
I've removed the 'Dim TryRun As Long' and added the messagebox. As of now, I'm getting a messagebox pop up with the value of Textbox1. Right after that, the random value I entered in Textbox2 gets overwritten by "No match found". No value has been pasted on the actual sheet. — G. Koen

Ahmed AU Ahmed AU · Accepted Answer · 2018-10-17T06:55:58

As Evaluate is for running worksheet function formulas, not for VBA as commented by @Tim Williams. There are numerous way to solve the problem otherwise

You may try WorksheetFunction.Match

Dim TryRun As Long
On Error Resume Next
TryRun = WorksheetFunction.Match(TextBox1.Value, Sheet1.Range("B14:B33"), 0)
On Error GoTo 0

    If TryRun > 0 Then
    TextBox2.Text = Sheet1.Range("G" & 13 + TryRun).Value
    Else
    TextBox2.Text = " No Match found"
    End If

Or try WorksheetFunction.Vlookup

On Error Resume Next
TryRun = WorksheetFunction.VLookup(TextBox1.Text, Sheet1.Range("B14:G33"), 6, False)
' Vookup consider 1str column as 1, Col G will be 6
On Error GoTo 0

    If IsEmpty(TryRun) Then
    TextBox2.Text = " No Match found"
    Else
    TextBox2.Text = TryRun
    End If

A word of caution, it is assumed that only text type data is being searched. For numeric type of data it please use Val(TextBox1.Text)

Offset Match function in VBA/Userform

1 Answers