UserForm won't process because VBA is too long!!

[PDBartlett]

Hello

I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.

When I tested it, I hit the submit button but it says the procedure is too large!

Here is the code which is repeated 143 times but with a different row number and range each time (in red):

Private Sub cmdSubmitPL_Click()
Dim ws As Worksheet:    Set ws = Worksheets("MASTER")
Dim i As Integer
    If ComboBox3.Value = Worksheets("PLHeadings").Range("C4").Value Then
        For i = 1 To 12
        If Me.Controls("txtY1M" & i).Value <> "" Then
        ws.Cells(17, 19 + i).Value = Me.Controls("txtY1M" & i).Value
        End If
        Next i
        For i = 1 To 12
        If Me.Controls("txtY2M" & i).Value <> "" Then
        ws.Cells(17, 31 + i).Value = Me.Controls("txtY2M" & i).Value
        End If
        Next i
        For i = 1 To 12
        If Me.Controls("txtY3M" & i).Value <> "" Then
        ws.Cells(17, 43 + i).Value = Me.Controls("txtY3M" & i).Value
        End If
        Next i
        For i = 1 To 12
        If Me.Controls("txtY3M" & i).Value <> "" Then
        ws.Cells(17, 55 + i).Value = Me.Controls("txtY4M" & i).Value
        End If
        Next i
        For i = 1 To 12
        If Me.Controls("txtY3M" & i).Value <> "" Then
        ws.Cells(17, 67 + i).Value = Me.Controls("txtY5M" & i).Value
        End If
        Next i

So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.

The range is always going to increase nice and easily: C5 up to C146.
The row number is a bit hit and miss.

Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?

Thanks

[romperstomper]

Is there any pattern to the row numbers? Or can it be calculated somehow inside a loop?

BTW, the code you posted doesn't seem particularly efficient - it looks like you could use just one loop:

        For i = 1 To 12
        If Me.Controls("txtY1M" & i).Value <> "" Then ws.Cells(17, 19 + i).Value = Me.Controls("txtY1M" & i).Value
        If Me.Controls("txtY2M" & i).Value <> "" Then ws.Cells(17, 31 + i).Value = Me.Controls("txtY2M" & i).Value
        If Me.Controls("txtY3M" & i).Value <> "" Then ws.Cells(17, 43 + i).Value = Me.Controls("txtY3M" & i).Value
        If Me.Controls("txtY4M" & i).Value <> "" Then ws.Cells(17, 55 + i).Value = Me.Controls("txtY4M" & i).Value
        If Me.Controls("txtY5M" & i).Value <> "" Then ws.Cells(17, 67 + i).Value = Me.Controls("txtY5M" & i).Value
        Next i

Note the amendments in red - are they correct? If so, you could add another loop to replace the 5 If statement lines.

[PDBartlett]

Ragulduy:

The row numbers are different from the range and aren't consistent enough to be used in a 'Next' function.

See my above post, would that work?

[PDBartlett]

Romperstomper, I just realised your amendments in red and that my code is wrong! I'll change now thanks

[ragulduy]

Try something like:

Private Sub cmdSubmitPL_Click()
Dim ws As Worksheet
Dim i As Integer, j As Integer
Set ws = Sheets("MASTER")
For j = 4 To 146
    If combobox3.Value = Worksheets("PLHeadings").Range("C" & j) Then
            For i = 1 To 12
                If Me.Controls("txtY1M" & i).Value <> "" Then
                    ws.Cells(j + 13, 19 + i).Value = Me.Controls("txtY1M" & i).Value
                End If
                If Me.Controls("txtY2M" & i).Value <> "" Then
                    ws.Cells(j + 13, 31 + i).Value = Me.Controls("txtY2M" & i).Value
                End If
                If Me.Controls("txtY3M" & i).Value <> "" Then
                    ws.Cells(j + 13, 43 + i).Value = Me.Controls("txtY3M" & i).Value
                    ws.Cells(j + 13, 55 + i).Value = Me.Controls("txtY4M" & i).Value
                    ws.Cells(j + 13, 67 + i).Value = Me.Controls("txtY5M" & i).Value
                End If
            Next
        Next j
    End If
Next j
End Sub

[PDBartlett]

Thanks romperstomper

I just thought, in order to create a pattern for the row numbers, would it best to get this code to 'paste' them onto a different sheet, whereby the rows will ALWAYS increase 1 by 1 by 1... and have the MASTER sheet (where the UserForm is currently 'pasting' to) equal the correct cell from this new sheet? That way the code for the row number will be something like 17 up to 163?

And yes, the workbook has 5 years worth of data

[ragulduy]

I would try curve-fitting your row number data to see if you can come up with an equation for them to use in a loop, otherwise I guess you could do something like this or store the numbers in a worksheet like you suggest:

Private Sub cmdSubmitPL_Click()
Dim ws As Worksheet
Dim i As Integer, j As Integer
Dim row_arr As Variant
row_arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, _
48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, _
75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, _
101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, _
121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, _
141, 142, 143)
Set ws = Sheets("MASTER")
For j = 1 To 143
    If combobox3.Value = Worksheets("PLHeadings").Range("C" & j + 3) Then
            For i = 1 To 12
                If Me.Controls("txtY1M" & i).Value <> "" Then
                    ws.Cells(row_arr(j), 19 + i).Value = Me.Controls("txtY1M" & i).Value
                End If
                If Me.Controls("txtY2M" & i).Value <> "" Then
                    ws.Cells(row_arr(j), 31 + i).Value = Me.Controls("txtY2M" & i).Value
                End If
                If Me.Controls("txtY3M" & i).Value <> "" Then
                    ws.Cells(row_arr(j), 43 + i).Value = Me.Controls("txtY3M" & i).Value
                    ws.Cells(row_arr(j), 55 + i).Value = Me.Controls("txtY4M" & i).Value
                    ws.Cells(row_arr(j), 67 + i).Value = Me.Controls("txtY5M" & i).Value
                End If
            Next
    End If
Next j
End Sub

[romperstomper]

Are you always comparing combobox3 against the cell in column C? If so, will there be more than one match?

[PDBartlett]

Combobox3 will always equal something from column C.

I just tested your code Ragulduy and it works perfectly!

[PDBartlett]

Guys I can't quite get this working as I altered it slightly:

Private Sub cmbTestSub_Click()
Dim ws As Worksheet
Dim i As Integer, r As Integer, col As Integer
Set ws = Sheets("Sub Master")
For col = 1 To 143
If ComboBox3.Value = Worksheets("PLHeadings").Range("C" & col + 3) Then
For i = 1 To 12
For r = 3 To 145
    If Me.Controls("txtY1M" & i).Value <> "" Then ws.Cells(r, 2 + i).Value = Me.Controls("txtY1M" & i).Value
    If Me.Controls("txtY2M" & i).Value <> "" Then ws.Cells(r, 14 + i).Value = Me.Controls("txtY2M" & i).Value
    If Me.Controls("txtY3M" & i).Value <> "" Then ws.Cells(r, 26 + i).Value = Me.Controls("txtY3M" & i).Value
    If Me.Controls("txtY4M" & i).Value <> "" Then ws.Cells(r, 38 + i).Value = Me.Controls("txtY4M" & i).Value
    If Me.Controls("txtY5M" & i).Value <> "" Then ws.Cells(r, 50 + i).Value = Me.Controls("txtY5M" & i).Value
Next r
Next i
End If
End Sub

The range (C4, 5, 6 etc) ranges from C4 to C146 and the userform needs to paste the data on the Sub Master starting on Row 3 going down to 145

[ragulduy]

I think the for col.. and for r.. loops should be combined? So instead of r you would use col+2?

[PDBartlett]

I don't understand?

If I run the above code I get an error: For without Next

[ragulduy]

True, you don't have a "next col" statement in the code either.

However, I'm not sure about the structure of your code, I thought that you would need to use the col value within the logic assigning the textbox values. As you have written it, the col and r loops are unneccessary, i.e.:

For col = 1 To 143
If ComboBox3.Value = Worksheets("PLHeadings").Range("C" & col + 3) Then
For i = 1 To 12
For r = 3 To 145
    If Me.Controls("txtY1M" & i).Value <> "" Then ws.Cells(r, 2 + i).Value = Me.Controls("txtY1M" & i).Value
    If Me.Controls("txtY2M" & i).Value <> "" Then ws.Cells(r, 14 + i).Value = Me.Controls("txtY2M" & i).Value
    If Me.Controls("txtY3M" & i).Value <> "" Then ws.Cells(r, 26 + i).Value = Me.Controls("txtY3M" & i).Value
    If Me.Controls("txtY4M" & i).Value <> "" Then ws.Cells(r, 38 + i).Value = Me.Controls("txtY4M" & i).Value
    If Me.Controls("txtY5M" & i).Value <> "" Then ws.Cells(r, 50 + i).Value = Me.Controls("txtY5M" & i).Value
Next r
Next i
Next col

looks to me, the same as:

If CountIf(Worksheets("PLHeadings").Range("C" & col + 3), ComboBox3.Value) > 1 Then
For i = 1 To 12
    If Me.Controls("txtY1M" & i).Value <> "" Then ws.Range(Cells(3, 2 + i), Cells(145, 2 + i)).Value = Me.Controls("txtY1M" & i).Value
    If Me.Controls("txtY2M" & i).Value <> "" Then ws.Range(Cells(3, 14 + i), Cells(145, 14 + i)).Value = Me.Controls("txtY2M" & i).Value
    If Me.Controls("txtY3M" & i).Value <> "" Then ws.Range(Cells(3, 26 + i), Cells(145, 26 + i)).Value = Me.Controls("txtY3M" & i).Value
    If Me.Controls("txtY4M" & i).Value <> "" Then ws.Range(Cells(3, 38 + i), Cells(145, 38 + i)).Value = Me.Controls("txtY4M" & i).Value
    If Me.Controls("txtY5M" & i).Value <> "" Then ws.Range(Cells(3, 50 + i), Cells(145, 50 + i)).Value = Me.Controls("txtY5M" & i).Value
Next i
End If

[PDBartlett]

With this code I get the error: Sub or Function not defined

And it highlights CountIf

Private Sub cmbTestSub_Click()
Dim ws As Worksheet
Dim i As Integer, r As Integer, col As Integer
Set ws = Sheets("Sub Master")
For col = 1 To 143
If CountIf(Worksheets("PLHeadings").Range("C" & col + 3), ComboBox3.Value) > 1 Then
For i = 1 To 12
    If Me.Controls("txtY1M" & i).Value <> "" Then ws.Range(Cells(3, 2 + i), Cells(145, 2 + i)).Value = Me.Controls("txtY1M" & i).Value
    If Me.Controls("txtY2M" & i).Value <> "" Then ws.Range(Cells(3, 14 + i), Cells(145, 14 + i)).Value = Me.Controls("txtY2M" & i).Value
    If Me.Controls("txtY3M" & i).Value <> "" Then ws.Range(Cells(3, 26 + i), Cells(145, 26 + i)).Value = Me.Controls("txtY3M" & i).Value
    If Me.Controls("txtY4M" & i).Value <> "" Then ws.Range(Cells(3, 38 + i), Cells(145, 38 + i)).Value = Me.Controls("txtY4M" & i).Value
    If Me.Controls("txtY5M" & i).Value <> "" Then ws.Range(Cells(3, 50 + i), Cells(145, 50 + i)).Value = Me.Controls("txtY5M" & i).Value
Next i
End If
End Sub

[ragulduy]

Sorry, it should be worksheetfunction.countif

[PDBartlett]

Error: For without Next

Haha I don't where it could be wrong??

Private Sub cmbTestSub_Click()
Dim ws As Worksheet
Dim i As Integer, r As Integer, col As Integer
Set ws = Sheets("Sub Master")
For col = 1 To 143
    If WorksheetFunction.CountIf(Worksheets("PLHeadings").Range("C" & col + 3), ComboBox3.Value) > 1 Then
For i = 1 To 12
    If Me.Controls("txtY1M" & i).Value <> "" Then ws.Range(Cells(3, 2 + i), Cells(145, 2 + i)).Value = Me.Controls("txtY1M" & i).Value
    If Me.Controls("txtY2M" & i).Value <> "" Then ws.Range(Cells(3, 14 + i), Cells(145, 14 + i)).Value = Me.Controls("txtY2M" & i).Value
    If Me.Controls("txtY3M" & i).Value <> "" Then ws.Range(Cells(3, 26 + i), Cells(145, 26 + i)).Value = Me.Controls("txtY3M" & i).Value
    If Me.Controls("txtY4M" & i).Value <> "" Then ws.Range(Cells(3, 38 + i), Cells(145, 38 + i)).Value = Me.Controls("txtY4M" & i).Value
    If Me.Controls("txtY5M" & i).Value <> "" Then ws.Range(Cells(3, 50 + i), Cells(145, 50 + i)).Value = Me.Controls("txtY5M" & i).Value
Next i
End If
End Sub

[Trebor76]

Try this:

Option Explicit
Private Sub cmbTestSub_Click()
    
    Dim ws As Worksheet
    Dim i As Long, r As Long, col As Long
    
    Set ws = Sheets("Sub Master")
    
    For col = 1 To 143
        If WorksheetFunction.CountIf(Worksheets("PLHeadings").Range("C" & col + 3), ComboBox3.Value) > 1 Then
            For i = 1 To 12
                If Me.Controls("txtY1M" & i).Value <> "" Then ws.Range(Cells(3, 2 + i), Cells(145, 2 + i)).Value = Me.Controls("txtY1M" & i).Value
                If Me.Controls("txtY2M" & i).Value <> "" Then ws.Range(Cells(3, 14 + i), Cells(145, 14 + i)).Value = Me.Controls("txtY2M" & i).Value
                If Me.Controls("txtY3M" & i).Value <> "" Then ws.Range(Cells(3, 26 + i), Cells(145, 26 + i)).Value = Me.Controls("txtY3M" & i).Value
                If Me.Controls("txtY4M" & i).Value <> "" Then ws.Range(Cells(3, 38 + i), Cells(145, 38 + i)).Value = Me.Controls("txtY4M" & i).Value
                If Me.Controls("txtY5M" & i).Value <> "" Then ws.Range(Cells(3, 50 + i), Cells(145, 50 + i)).Value = Me.Controls("txtY5M" & i).Value
            Next i
        End If
    Next col

End Sub

Robert

[PDBartlett]

I don't get any error messages with that but it doesn't paste the values onto the sheet?

[ragulduy]

I didn't notice you added the for col... bit, I think you need to upload a workbook with an example of what your data looks like and a description of what you are trying to achieve, and what the end result should be like.

To be honest, I'm not really sure what you are trying to do at this point!

[Trebor76]

I don't get any error messages with that but it doesn't paste the values onto the sheet?

I cannot answer that from my end? Try stepping through the code to see if that helps.

[PDBartlett]

Ok, so the code should be doing:

If combobox3 equals any of the cells in range C4:C146 Then
'Paste' whatever the user has inputted in the textboxes on the UserForm into the relevant cell in the sheet 'sub master'

These cells equal:
Row 3 to 145
Columns 3 to 62 (5 years)

See attached a much simplified version of my workbook

Forum Upload.xlsm

[ragulduy]

I would use something like:

Private Sub cmbTestSub_Click()
Dim r_num, m_num, y_num, count
m_num = 1
y_num = 1
r_num = Sheets("Sub Master").Columns(3).Find(Me.ComboBox3.Value).Row
For count = Range("C1").Column To Range("BJ1").Column
    Cells(r_num, count) = Me.Controls("txtY" & y_num & "M" & m_num).Value
    m_num = m_num + 1
    If m_num > 12 Then
        y_num = y_num + 1
        m_num = 1
    End If
Next count
End Sub