CODEWITHSUNDEEP
excelvba
MatthewZ
MatthewZ

Reputation: 13

How to print the value of a cell you find into a text box in VBA

enter image description hereWe are trying to write a code that, given a site name (column) and SKU (row), the value of the resulting cell gets shown in a text box. We've gotten to the point where we have the cell number, ie. E16, but we can't figure out how to print the value of E16 into the text box. txtPrice.value in the last line is supposed to be what gets posted in the text box, but it keeps coming out empty

Private Sub btnGO_Click()
Dim rowNum, cellNum As Variant
rowNum = Application.WorksheetFunction.Match(txtSKU.Value, 
Worksheets("AllSites").Range("B:B"), 0)
If IsError(rowNum) Then MsgBox "SKU not found": Exit Sub
If cbxSite.Value = "Fairburn" Then
    cellNum = "C" & rowNum
        ElseIf cbxSite.Value = "Aberdeen" Then
    cellNum = "D" & rowNum
        ElseIf cbxSite.Value = "University Park" Then
    cellNum = "E" & rowNum
        ElseIf cbxSite.Value = "Roanoke" Then
    cellNum = "F" & rowNum
        ElseIf cbxSite.Value = "Lathrop" Then
    cellNum = "G" & rowNum
        Else: cbxSite.Value = "Redlands"
    cellNum = "H" & rowNum
If IsError(cellNum) Then MsgBox "Site not found": Exit Sub
txtPrice.Value = Worksheets("AllSites").Cells(cellNum).Value
End If
End Sub

Upvotes: 1

Views: 1444

Answers (3)

codeLearner
codeLearner

Reputation: 86

Try this: 

Textbox.Text = Worksheets("AllSites").Cells(cellNum).Value

Upvotes: 0

J. Garth
J. Garth

Reputation: 803

The problem is with the structure of your If statement. Your End If statement is in the wrong place. This caused Excel to insert a colon after your Else statement. Using a colon is a way to put two statements on the same line. So the effect of this is that if none of the other If & ElseIf condtions are met, then this statement cellNum = "H" & rowNum will always execute and probably directs cellNum to a blank cell.

Even if one of the other If conditions is met, then the code is skipping the part that sets the textbox value because everything between Else: and End If is part of the Else: clause so those statements don't get executed if any of the other If conditions evaluate to True.

So you need to change the Else: condition to antoher ElseIf and move the End If statement below that block.

Additionally, you need to change .Cells to .Range as others here have noted.

Private Sub btnGO_Click()

    Dim rowNum, cellNum As Variant

    rowNum = Application.WorksheetFunction.Match(txtSKU.Value, Worksheets("AllSites").Range("B:B"), 0)

    If IsError(rowNum) Then MsgBox "SKU not found": Exit Sub

    If cbxSite.Value = "Fairburn" Then
        cellNum = "C" & rowNum
    ElseIf cbxSite.Value = "Aberdeen" Then
        cellNum = "D" & rowNum
    ElseIf cbxSite.Value = "University Park" Then
        cellNum = "E" & rowNum
    ElseIf cbxSite.Value = "Roanoke" Then
        cellNum = "F" & rowNum
    ElseIf cbxSite.Value = "Lathrop" Then
        cellNum = "G" & rowNum
    ElseIf cbxSite.Value = "Redlands" Then
        cellNum = "H" & rowNum
    End If

    If IsError(cellNum) Then MsgBox "Site not found": Exit Sub

    txtPrice.Value = Worksheets("AllSites").Range(cellNum).Value

End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

You are trying to use something like Cells("H6").Value. It should be .Range("H6").Value.

'where cellNum is a string something like "H6"
txtPrice.Value = Worksheets("AllSites").Range(cellNum).Value

Upvotes: 1

Related Questions