Results 1 to 4 of 4

Thread: how to substitute a column reference for an If statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Location
    Melbourne
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default how to substitute a column reference for an If statement

    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!!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: how to substitute a column reference for an If statement

    Try this


    Code:
        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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    May 2019
    Location
    Melbourne
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: how to substitute a column reference for an If statement

    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”)

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: how to substitute a column reference for an If statement

    Quote Originally Posted by Glenn C View Post
    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

    Code:
        If ComboBox5 = "closed top" Then
           varx = "C"
        ElseIf ComboBox5 = "open top" Then
            varx = "D"
        ElseIf ComboBox5 = "over height" Then
            varx = "E"
        ElseIf ComboBox5 = "Jumbo" Then
            varx = "F"
        End If
        
        If varx  <> "" 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, varx).Value, "0.00")
            End If
        End If
    Last edited by DanteAmor; May 27th, 2019 at 09:28 PM.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •