Using the Same Code for different IF Values

[Denny57]

Following on from an earlier equiry I have successfully managed to reduce over 2500 lines of code by about 90%, however, I believe that this can be further reduced by about another 90% as the code is repeated 8 times, once for every value in a ComboBox. The only difference to each "Block" is the target column range. I have 2 similar questions which I hope can be resolved without the need to re-write much of the code.

Here is a sample of the code showing two of the eight CBO Variable Values

Private Sub cmdThunderballCallDetails_Click()
Dim vCols As Variant
Dim lngRowLoop As Long
Dim lngCtrlLoop As Long
Dim tbCounter As Long

    If cboTBallDrawNumber.Value = "Draw 1" Then
    Set ctn = Sheets("Core Ticket Numbers")
     tbCounter = 1
    vCols = Array("B", "C", "D", "E", "F", "G")
    For lngRowLoop = 44 To 56
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("txtThunderballSelection" & tbCounter).Text = ctn.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
       Next
    Next
    Set vtd = Sheets("Variable Ticket Details")
    tbCounter = 1
    vCols = Array("C")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballMethodLine" & tbCounter).Text = vtd.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
    tbCounter = 1
    vCols = Array("D")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballStatusLine" & tbCounter).Text = vtd.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
    End If

    If cboTBallDrawNumber.Value = "Draw 2" Then
    Set ctn = Sheets("Core Ticket Numbers")
    tbCounter = 1
    vCols = Array("H", "I", "J", "K", "L", "M")
    For lngRowLoop = 44 To 56
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("txtThunderballSelection" & tbCounter).Text = ctn.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
       Next
    Next
    Set vtd = Sheets("Variable Ticket Details")
      tbCounter = 1
    vCols = Array("E")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballMethodLine" & tbCounter).Text = vtd.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
    tbCounter = 1
    vCols = Array("F")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballStatusLine" & tbCounter).Text = vtd.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
    End If

Question 1 = Is there a way to merge the 2 items for Set vtd = Sheets("Variable Ticket Details")? I have tried a number of combinations, none of which were acceptable. The only differences are the target ComboBoxes and the source columns in the same Worksheet.

Question 2 = The only differences in each block are the Column Arrays and the data in cboTBallDrawNumber. The Values of this will be "Draw 1", "Draw 2" .....to "Draw 8". I feel sure that VBA must support the ability to include a variable that would look up the corresponding Arrays.

Please note that the row numbers remain the same for all "cboTBallDrawNumber" variables

As my code works and is much smaller than it was then I am happy for this to remain, But from a learning perspective, it would be useful to ask if this is possible before I start trying some ideas for how this might work.

Many thanks

 

[Micron]

Not sure you interpreted my suggestion correctly. I meant another column in the same combo. You're creating the arrays in code with values like ABC so my idea was to just pick 2 from the combo and this new column would therefore pass DEF or whatever to your code. If you have to type the array values in code then I don't see why those values can't just be in this added column. That way you eliminate the array stuff.
Regardless, I think you've decided to stay the course for now and I can respect that. Good luck with it!

 

[Denny57]

Not sure you interpreted my suggestion correctly. I meant another column in the same combo. You're creating the arrays in code with values like ABC so my idea was to just pick 2 from the combo and this new column would therefore pass DEF or whatever to your code. If you have to type the array values in code then I don't see why those values can't just be in this added column. That way you eliminate the array stuff.
Regardless, I think you've decided to stay the course for now and I can respect that. Good luck with it!

In closing, I have now have some understanding of generatingf mutiple columns in a ComboBox, but I still need to unserstand how to show each column in the IF statement. Also, the Array code uses vCols as a Dim and this is included in the Loop Code, so I assume this Dim would need to refer to the additional column of the ComboBox. in the IF statement.

If this is correct, I would still have the to create 8 instances as the IF / ELSEIF I tried did not work.

Once again thanks you for your help with this.

 

[Denny57]

Sorry to disturb you again but I am now having a problem whereby I get a "Variable not Defined" error message for the final piece of code I have added under the previous code. I have used the same syntax code thousand of times without an issue. These 6 lines have been added to replace the loop I was attempting for a single row of data. Note, the data for this is called from a third worksheet. The textboxes are on the same UserForm. I have tried prefixing the contols with Me.Controls and placing then in parenthesis and quotation marks but still get the same error message..

This is not improving my headache but I am determined to understand how these commands should be coded.

Thanks in advance

Option Explicit
Dim ws As Worksheet

Private Sub cmdThunderballCallDetails_Click()
Dim vCols As Variant
Dim lngRowLoop As Long
Dim lngCtrlLoop As Long
Dim tbCounter As Long


    If cboTBallDrawNumber.Value = "Draw 1" Then
    Set ws = Sheets("Core Ticket Numbers")
     tbCounter = 1
    vCols = Array("B", "C", "D", "E", "F", "G")
    For lngRowLoop = 44 To 56
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("txtThunderballSelection" & tbCounter).Text = ws.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
       Next
    Next
    Set ws = Sheets("Variable Ticket Details")
    tbCounter = 1
    vCols = Array("C")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballMethodLine" & tbCounter).Text = ws.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
    tbCounter = 1
    vCols = Array("D")
    For lngRowLoop = 46 To 58
        For lngCtrlLoop = 0 To UBound(vCols)
        Me.Controls("cboTballStatusLine" & tbCounter).Text = ws.Range(vCols(lngCtrlLoop) & lngRowLoop).Value
        tbCounter = tbCounter + 1
        Next
    Next
       
    Set ws = Sheets("Draw Result Details")
    
        txtTBallResultsBall1.Text = ws.Range("D25").Value
        txtTBallResultsBall2.Text = ws.Range("E25").Value
        txtTBallResultsBall3.Text = ws.Range("F25").Value
        txtTBallResultsBall4.Text = ws.Range("G25").Value
        txtTBallResultsBall5.Text = ws.Range("H25").Value
        txtTBallResultsBall6.Text = ws.Range("I25").Value
        
   
    End If
    
End Sub

 

[Micron]

Always nice to know what line raises an error, and what part gets highlighted if applicable. Given the error, I'd say you do get highlighting.

This code is in the userform module? Then Me.txtTBallResultsBall1.Text = ws.Range("D25") should work.
If you're looking to condense code, no need to specify Value about 99% of the time.

 

[Denny57]

Thank you. I have just identified the problem.

I promise I will not trouble you again with this issue

 

[Micron]

I promise I will not trouble you again with this issue

If I gave you the impression that you were doing so it was not my intention. There's no problem with asking.
Usually it's nice to see or at least know of your solution as it may help future readers of your thread.

 

[Denny57]

If I gave you the impression that you were doing so it was not my intention. There's no problem with asking.
Usually it's nice to see or at least know of your solution as it may help future readers of your thread.

Not at all.. I am very grateful of the help. I do not normally need to keep reverting back on responses, but I think I bit off a bit more than my VBA knowledge will allow with this project

 

[Denny57]

Hi I managed to get this solution which I thought might be useful for reference.

Option Explicit

Dim ws As Worksheet
Private Sub UserForm_Initialize()

End Sub

Private Sub cmdThunderballCallDetails_Click()
    Dim cbToColsDict As Object
    
        Set cbToColsDict = CreateObject("Scripting.Dictionary")
        
            With cbToColsDict
                .Add "Draw 1", Array("B", "C", "D", "E", "F", "G")
                .Add "Draw 2", Array("H", "I", "L", "K", "L", "M")
                .Add "Draw 3", Array("N", "O", "P", "Q", "R", "S")
                .Add "Draw 4", Array("T", "U", "V", "W", "X", "Y")
                .Add "Draw 5", Array("Z", "AA", "AB", "AC", "AD", "AE")
                .Add "Draw 6", Array("AF", "AG", "AH", "AI", "AJ", "AK")
                .Add "Draw 7", Array("AL", "AM", "AN", "AO", "AP", "AQ")
                .Add "Draw 8", Array("AR", "AS", "AT", "AU", "AV", "AW")
            End With
        With Me
            Dim vCols As Variant
                vCols = cbToColsDict(.cboTBallDrawNumber.Value)
            Set ws = Sheets("Core Ticket Numbers")
            Dim tbCounter As Long
                tbCounter = 1
            Dim lngRowLoop As Long
                    For lngRowLoop = 44 To 56
                        Dim vCol As Variant
                            For Each vCol In vCols
                                .Controls("txtThunderballSelection" & tbCounter).Text = ws.Cells(lngRowLoop, vCol).Value
                                tbCounter = tbCounter + 1
                            Next
                    Next
         End With
End Sub