Pulling specific cell data based on a textbox aplha numeric code

[DanSMT]

All,

I am having an issue pulling a specific number back from an array of data.

The thought is to have a "lot value" column and then "Sample" and "Reject" columns. Based on the entry into the userform textbox I want it to select the data based on the lot size entered into a different textbox on the userform. Sample spreadsheet image below;

I am struggling to pull back the correct data and am a little lost. Any help would be great.

Code below;

Worksheets("RI Codes").Activate
If LotSizeTB.Value = 1 Then
SampleTB.Value = 1
RejectTB.Value = 1
Else
Col = Range("" & Left(RICodeTB, 1) & "" & 1).Column
With Worksheets("RI Codes").ListObjects(1).DataBodyRange
    X = Evaluate("=MATCH(TRUE," & .Columns(1).Address & ">=" & Val(LotSizeTB) & ",0)")
    If RICodeTB = "P1" Then
    SampleTB.Value = 1
    RejectTB.Value = 0
    ElseIf Not IsError(X) Then
        SampleTB = .Cells(X, Left(RICodeTB, 1) + 2)
        'SampleTB = .Cells(X, Col - 9) 'sample size
        RejectTB = .Cells(X, Right(RICodeTB, 1) + 2) 'reject qty
    Else
        MsgBox "SAMPLE SIZE IS NOT WITHIN TABLE PARAMETERS", vbInformation, ""
    End If
End With
End If

 

[DanSMT]

This would be an example.

If LotSizeTB = 174
and if value of RICodeTB = A2 then;

I want SampleTB = 13
and RejectTB = 2

Based on the spreadsheet above.

 

[DanSMT]

The code was worked a few times and then stopped. Not sure what is going on, but IIve revised the above code to the follow. Ran a few iterations and then it started to pull incorrect information.

I've also removed the P1 column which helped.

Code below;

Private Sub LotSizeTB_AfterUpdate()
Dim X, Col As Long
If RICodeTB = "" Then
    MsgBox "RI Code not assigned, contact Quality Manager", vbInformation, ""
    RICodeTB.SetFocus
    Exit Sub
End If
Col = Range("" & Left(RICodeTB, 1) & "" & 1).Column
With Sheet1.ListObjects(1).DataBodyRange
    X = Evaluate("=MATCH(TRUE," & .Columns(1).Address & ">=" & Val(LotSizeTB) & ",0)")
    If Not IsError(X) Then
        SampleTB = .Cells(X, Col + 1) 'sample size
        RejectTB = .Cells(X, Right(RICodeTB, 1) + 4) 'reject qty
    Else
        MsgBox "SAMPLE SIZE IS NOT WITHIN TABLE PARAMETERS", vbInformation, ""
    End If
End With
If SampleTB.Value > "" Then
TextBox179.Value = SampleTB.Value
End If
Worksheets("ri log").Activate
End Sub

Any assistance would be greatly appreciated.

Thank you in advance!

 

[DanSMT]

Fixed it!

The problem persisted because I was not activating/showing the RI Codes sheet. I added a worksheets.activate command and everything is working smoothly now.

Attached code for reference.

Private Sub LotSizeTB_AfterUpdate()
Dim X, Col As Long
Application.ScreenUpdating = False
Worksheets("RI Codes").Activate
If RICodeTB = "" Then
    MsgBox "RI Code not assigned, contact Quality Manager", vbInformation, ""
    RICodeTB.SetFocus
    Exit Sub
End If
Col = Range("" & Left(RICodeTB, 1) & "" & 1).Column
With Worksheets("RI Codes").ListObjects("SampleSize").DataBodyRange
    X = Evaluate("=MATCH(TRUE," & .Columns(1).Address & ">=" & Val(LotSizeTB) & ",0)")
    If Not IsError(X) Then
        SampleTB = .Cells(X, Col + 1) 'sample size
        RejectTB = .Cells(X, Right(RICodeTB, 1) + 4) 'reject qty
    Else
        MsgBox "SAMPLE SIZE IS NOT WITHIN TABLE PARAMETERS", vbInformation, ""
    End If
End With
If SampleTB.Value > "" Then
TextBox179.Value = SampleTB.Value
End If
Worksheets("ri log").Activate
Application.ScreenUpdating = True
End Sub