How to combine Row Letter with Integer to determine cell value

[Zerb]

I have a sheet that has two columns in it, A and B. A has part numbers and B has quantities. I have populated my part numbers into a ComboBox on a UserForm and, at least this is what I think is the best way to do it, I want to use the .ListIndex of that ComboBox to then get the value from the column B in the worksheet so that I can populate another ComboBox. So as an example if the .ListIndex is 4, then I want to get the value of B4 into my VBA as an Integer so I can use that value to populate my second ComboBox. Which will end up being 1-FoundIntegerValue.

Private Sub PartNumberBox_Change()
Dim Max As Integer


If PartNumberBox.ListIndex <> -1 Then
    QuantityBox.Visible = True
    JobNumberBox.Visible = True
Else
    QuantityBox.Visible = False
    JobNumberBox.Visible = False
End If

Max = Worksheets("OnHand").Cell("B" & PartNumberBox.ListIndex).Offset(1)



End Sub

 

[Osvaldo Palmeiro]

Hi.
Have you tried?

Max = Worksheets("OnHand").Cells(PartNumberBox.ListIndex, "B")

or

Max = Worksheets("OnHand").Range("B" & PartNumberBox.ListIndex)

 

[Zerb]

I think I have tried the latter but I'd have to go back and look, thanks for the idea! I got it taken care of thought doing it this way as shown below.

The other issue I seemed to run into was that since the .ListIndex starts at 0 for its first value, that actually needed to be offset in my case since my Part Numbers and Quantities don't start until Row 2 on the sheet. Once I setup some Debug.Prints I caught it and sorted it out.

Private Sub PartNumberBox_Change()
Dim Max As Integer
Dim Row As Integer
Dim Col As Integer
Dim Range As Variant

Let Col = 3


If PartNumberBox.ListIndex <> -1 Then
    QuantityBox.Visible = True
    JobNumberBox.Visible = True
Else
    QuantityBox.Visible = False
    JobNumberBox.Visible = False
End If

If PartNumberBox.ListIndex <> -1 Then
    Row = PartNumberBox.ListIndex + 2
    Set Range = Cells(Row, Col)
    Max = Range.Value
End If