how to substitute a column reference for an If statement

[Glenn C]

Hi

sht = Me.ComboBox15.ValueIf Sheets(sht).Cells(i, "A").Value = (sht) Or _
Sheets(sht).Cells(i, "A").Value = productCode Then
Me.TextBox13 = Format(Sheets(sht).Cells(i, "C").Value, "0.00")

I have the above Syntax that looks up a value on a worksheet, it works fine, but I would like to substitute the "C" (column reference) for this dynamic nested If statement:

If ComboBox5 = "closed top" Then
TextBox100 = "C"
ElseIf ComboBox5 = "open top" Then
TextBox100 = "D"
ElseIf ComboBox5 = "over height" Then
TextBox100 = "E"
ElseIf ComboBox5 = "Jumbo" Then
TextBox100 = "F"
End If


Thanks for any help!!

 

[DanteAmor]

Try this

    If ComboBox5 = "closed top" Then
        TextBox100 = "C"
    ElseIf ComboBox5 = "open top" Then
        TextBox100 = "D"
    ElseIf ComboBox5 = "over height" Then
        TextBox100 = "E"
    ElseIf ComboBox5 = "Jumbo" Then
        TextBox100 = "F"
    End If
    
    If TextBox100 <> "" Then
        sht = Me.ComboBox15.Value
        If Sheets(sht).Cells(i, "A").Value = (sht) Or _
            Sheets(sht).Cells(i, "A").Value = ProductCode Then
            Me.TextBox13 = Format(Sheets(sht).Cells(i, TextBox100).Value, "0.00")
        End If
    End If

 

[Glenn C]

Hi
Thanks Dante, sorry I should have mentioned that I’m trying to do without textbox100 and just put that formula into the code for text box13 (where it currently referenced “C”)

 

[DanteAmor]

Hi
Thanks Dante, sorry I should have mentioned that I’m trying to do without textbox100 and just put that formula into the code for text box13 (where it currently referenced “C”)

Then change it by any variable

    If ComboBox5 = "closed top" Then
       [COLOR=#0000ff]varx [/COLOR]= "C"
    ElseIf ComboBox5 = "open top" Then
        [COLOR=#0000ff]varx[/COLOR] = "D"
    ElseIf ComboBox5 = "over height" Then
        [COLOR=#0000ff]varx [/COLOR]= "E"
    ElseIf ComboBox5 = "Jumbo" Then
        [COLOR=#0000ff]varx [/COLOR]= "F"
    End If
    
    If [COLOR=#0000FF]varx [/COLOR] <> "" Then
        sht = Me.ComboBox15.Value
        If Sheets(sht).Cells(i, "A").Value = (sht) Or _
            Sheets(sht).Cells(i, "A").Value = ProductCode Then
            Me.TextBox13 = Format(Sheets(sht).Cells(i, [COLOR=#0000ff]varx[/COLOR]).Value, "0.00")
        End If
    End If