Userform Populate Textbox

[sweenem4]

I have a Textbox that I want autopopulated from a worksheet, based on the values in two other textboxes.

My worksheet has (3) columns: A (Yardage), B (Combinations), C (Price Per Yard).

If TextBox1 = 4,000yds and TextBox2 = 2combinations, then TextBox3 should be populated with $2.80.

The below formula always returns "Not found in database"

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng As Range, rng1 As Range, res As Variant
Set rng = Worksheets("Worksheet1").Range("A2:C50").Columns(1)
res = Application.Match(TextBox3.Text, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
TextBox1.Text = rng1.Offset(0, 1)
TextBox2.Text = rng1.Offset(0, 2)
Else
MsgBox "Not found in database"
End If
End Sub

Thanks for your help!

 

[Ralajer]

Just double checked this. When a value is not found with match() it returns a #NA which isn't strictly speaking an error, so IsError(#NA) = False. Try the ISNA Function which is accessible in VBA via Application.WorksheetFunction.IsNA(res).

 

[sweenem4]

Rob, thanks for the repy, but no luck.

TextBox1 should match to ColumnA in worksheet1; TextBox2 should match to ColumnB in worksheet1; and then when I tab out of TextBox3, it should return the cooresponding value in ColumnC from worksheet1.

My formula doesn't find my value and returns the else statement "Not found in database"

I'm just trying to perform an if statement. If textbox1 = columnA and if textbox2 = columnB, then columnC goes into textbox3, ELSE "Not found in database"

Any help would be appreciated. Thanks again!

 

[Ralajer]

Change:
Set rng = Worksheets("Worksheet1").Range("A2:C50").Columns(1)
To:
Set rng = Worksheets("Worksheet1").Range("A2:C50").Columns(3)

Setting rng using the first returns A2:A50 as Columns(1) is the first column in the Range("A2:C50")

Let me know if that works.

 

[Ralajer]

IsError will work confused VBA and Excel functions. Try this out I handle the ranges a little differently.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim rng As Range    '// TextBox 1-3 Source range
    Dim rng3 As Range   '// TextBox3 Match Range
    Dim res As Variant  '// Result of Match
    
    Set rng = Worksheets("Worksheet1").Range("A2:C50")
    Set rng3 = rng.Columns(3)
    
    res = Application.Match(TextBox3.Text, rng3, 0)
    
    If Not IsError(res) Then
        TextBox1.Text = rng(res, 1)
        TextBox2.Text = rng(res, 2)
    Else
        MsgBox "Not found in database"
    End If

End Sub

 

[sweenem4]

Thanks again, but I'm still having the same problem.

Textbox1 = Column A = Yards
Textbox2 = Column B = Combinations
Textbox3 = Column C = the answer that I want returned in this text box

Any other suggestions? A SumIf would work here, since there is only one possible answer for Textbox 3.

Thanks!

 

[Ralajer]

I think that I was more focused on the particular error you were getting in my previous posts and not exactly what you were trying to accomplish.

Based on the code it appears that you want to change the values of in Textbox1 and Textbox2 based on what is in Textbox3.

However, reading your requirements I think you want to populate Textbox3 based on what is Textbox1 and Textbox2.

Though, I think I understand what your requirement are could you clarify? Also how is the data on the worksheet arrange are there duplicate values in the columns such as.

A | B | C
4,000yds | 1combinations | 2.00
4,000yds | 2combinations | 2.80
4,000yds | 6combinations | 3.00
6,000yds | 1combinations | 3.00
6,000yds | 2combinations | 3.80
6,000yds | 6combinations | 4.00​

 

[sweenem4]

That is exactly what I am looking for.

When I enter 4,000 into TextBox1 and 2 into TextBox2, TextBox3 should return $2.80.

 

[sweenem4]

Also, there will be duplicated in Column A and then in Column B, but never the same number in Column A AND in Column B.

 

[Ralajer]

Try this and let me know if it works

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    SetTextBox3
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    SetTextBox3
End Sub

Private Sub SetTextBox3()
    
    If TextBox1.Text = "" Or TextBox2.Text = "" Then Exit Sub
    
    Dim yardRng As Range
    Dim cell As Range
    Dim price As Double
    
    price = -999.999    '// Arbitrary Default value
    
    Set yardRng = Worksheets("Sheet1").Range("A2:A50")

    For Each cell In yardRng
        If CStr(cell.Value) = TextBox1.Value And CStr(cell.Offset(0, 1)) = TextBox2 Then
            price = cell.Offset(0, 2)
            Exit For
        End If
    Next cell
    
    If price = -999.999 Then
        TextBox3.Text = "Not in database"
    Else
        TextBox3.Text = Format(price, "$#0.00")
    End If
End Sub