VBA Compile Error for each control variable must be variant or object

[jazzyelsie]

I have a vba code I am having issues with. The way it's supposed to function is a userform opens when the button is pressed. In the UF, there are four checkboxes: A only, B only, C only, and D only each of which should be selected if the user's data will contain that column (know as the master column). If so, the user is to enter the column letter it starts at in the text box next to it. Once the checkbox is clicked, a list of the potential column letters for the subcategories appears so the user can select where the data from the master column letter should be outputted into. Each Master column has a range it could fall into. When I run the code, nothing outputs. 

Thanks in advance for any help!

 Codes for the userform:

Private Sub chboxA_Click()

    TextBox1.Enabled = chboxA.Value  ' Toggle the enabling of TextBox1 based on chboxA's state

    lstA.Visible = chboxA.Value      ' Toggle the visibility of lstA based on chboxA's state

End Sub

Private Sub chboxB_Click()

    TextBox2.Enabled = chboxB.Value  ' Toggle the enabling of TextBox2 based on chboxB's state

    lstB.Visible = chboxB.Value      ' Toggle the visibility of lstB based on chboxB's state

End Sub

Private Sub chboxC_Click()

    TextBox3.Enabled = chboxC.Value  ' Toggle the enabling of TextBox3 based on chboxC's state

    lstC.Visible = chboxC.Value      ' Toggle the visibility of lstC based on chboxC's state

End Sub

Private Sub chboxD_Click()

    TextBox4.Enabled = chboxD.Value  ' Toggle the enabling of TextBox4 based on chboxD's state

    lstD.Visible = chboxD.Value      ' Toggle the visibility of lstD based on chboxD's state

End Sub

Private Sub UserForm_Initialize()

    ' Initialize ListBoxes with columns

    Dim col As Variant

    For Each col In Array("lstA", "lstB", "lstC", "lstD")

        With Me.Controls(col)

            .List = Array("I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W")

            .MultiSelect = fmMultiSelectMulti

            .Visible = False  ' Initialize all ListBoxes as hidden

        End With

    Next col

    ' Initialize the visibility based on checkboxes

    lstA.Visible = chboxA.Value

    lstB.Visible = chboxB.Value

    lstC.Visible = chboxC.Value

    lstD.Visible = chboxD.Value

    TextBox1.Enabled = chboxA.Value

    TextBox2.Enabled = chboxB.Value

    TextBox3.Enabled = chboxC.Value

    TextBox4.Enabled = chboxD.Value

End Sub

Private Sub cmdProcessData_Click()

    Application.ScreenUpdating = False  ' Disable screen updating to enhance performance

    Application.Calculation = xlCalculationManual  ' Avoid recalculation until the end for performance

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("BidTrial")

    Dim i As Integer

    Dim targetCols As String

    Dim masterCol As String

    Dim colLetter As String

    For i = 1 To 4

        ' Check if the corresponding checkbox is checked

        If Me.Controls("chbox" & Chr(64 + i)).Value Then

            colLetter = Me.Controls("TextBox" & i).Text

            ' Validate the column letter is correct and only allow single column letters

            If colLetter <> "" And IsColumnValid(colLetter) Then

                masterCol = colLetter & "10:" & colLetter & "121"  ' Data starts at row 10

                targetCols = GetSelectedRanges(Me.Controls("lst" & Chr(64 + i)))

                ' Copy data from master column to selected target columns

                If targetCols <> "" Then

                    CopyData ws, masterCol, targetCols

                End If

            Else

                MsgBox "Invalid column letter in TextBox" & i, vbExclamation

            End If

        End If

    Next i

    Application.Calculate  ' Recalculate all formulas in the workbook

    Application.ScreenUpdating = True  ' Re-enable screen updating

    MsgBox "Data processing complete!"

End Sub

Function IsColumnValid(col As String) As Boolean

    IsColumnValid = (col >= "A" And col <= "W") And Len(col) = 1  ' Ensure single letter between A and W

End Function

Function GetSelectedRanges(lstBox As MSForms.ListBox) As String

    Dim result As String

    Dim i As Integer

    For i = 0 To lstBox.ListCount - 1

        If lstBox.selected(i) Then

            If result <> "" Then result = result & ","

            result = result & lstBox.List(i) & "10:" & lstBox.List(i) & "121"  ' Specify full range for each selected column

        End If

    Next i

    GetSelectedRanges = result

End Function

Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String)

    Dim targetRange As Range, cell As Range, targetCell As Range

    Dim colRange As Variant

    For Each colRange In Split(targetCols, ",")

        Set targetRange = ws.Range(colRange)

        For Each cell In ws.Range(masterCol)

            If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then

                For Each targetCell In targetRange

                    If Not IsGrey(targetCell) Then

                        targetCell.Value = cell.Value

                    End If

                Next targetCell

            End If

        Next cell

    Next colRange

End Sub

Function IsGrey(cell As Range) As Boolean

    IsGrey = (cell.Interior.Color = RGB(128, 128, 128))  ' Check if the cell's color is grey

End Function 

 This is how my data looks and where it should be outputted into. For example, since A only has data, the user would enter H in the text box, then select each column that has A in it (L:O). Then the code would copy the data in H into those columns. For B only, the same would occur but only for headers with B (L:N).

 

 

The code runs, but does not output. When I try to update the copydata to this: 

Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String)

    Dim targetRange As Range, cell As Range, targetCell As Range

    Dim colRange As String

    ' Ensure that colRange is treated as a String here

    For Each colRange In Split(targetCols, ",")

        Set targetRange = ws.Range(colRange)

        For Each cell In ws.Range(masterCol)

            If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then

                For Each targetCell In targetRange.Cells  ' Make sure to iterate over Cells of targetRange

                    If Not IsGrey(targetCell) Then

                        targetCell.Value = cell.Value

                    End If

                Next targetCell

            End If

        Next cell

    Next colRange

End Sub

When I update the code, I tend to have issues with it running due to For Each colRange In Split(targetCols, ","). It's being used as a variant because of it iterating over an array resulting from the split function, however, there seems to be inconsistencies in how vba is handling it.  Not sure how to fix it.: 

I attached the sample document below. I also include the code that runs but doesn't process any data. then i commented the updated one that gives me the compile error: 

Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String)

    Dim targetRange As Range, cell As Range, targetCell As Range

    Dim colRange As String

    ' Ensure that colRange is treated as a String here

    For Each colRange In Split(targetCols, ",")

        Set targetRange = ws.Range(colRange)

        For Each cell In ws.Range(masterCol)

            If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then

                For Each targetCell In targetRange.Cells  ' Make sure to iterate over Cells of targetRange

                    If Not IsGrey(targetCell) Then

                        targetCell.Value = cell.Value

                    End If

                Next targetCell

            End If

        Next cell

    Next colRange

End Sub

[#2]

@jazzyelsie 

Maybe this change will help.

Inserted in the file.

If not, just ignore it .

[#3]

It didn't but I appreciate you trying! Thank you!

[#4]

@jazzyelsie 

Point 1: « When I run the code, nothing outputs [i.e., nothing is written to the target cells] »

It turns out that's not correct. The code is furiously writing values into the target cells. In your CopyData procedure, the second For Each loop picks up the value from row 10 of the Master column and conditionally starts the third For Each loop, which conditionally writes the value into row 10 of a target column, and then into row 11 of that target column, and then into row 12, and so on, and so on, till every cell of the target column (conditionally) has that row 10 value. Then the second For Each loop does the same from row 11, etc. You're only seeing nothing in the target cells during your tests because the last copyable master cell is empty.

That third For Each loop should not exist. As the target rows will always be the same rows as the master rows, you should replace

                For Each targetCell In targetRange
                    If Not IsGrey(targetCell) Then
                        targetCell.Value = cell.Value
                    End If
                Next targetCell

with

                Set targetCell = ws.Range(Left$(colRange, 1) & cell.Row)
                If Not IsGrey(targetCell) Then
                    targetCell.Value = cell.Value
                End If

You would see the problem if you stepped through the code in debugging mode. Here are some online resources on debugging:

• How to Debug in VBA (you won't use the Start button to begin execution, but breakpoints are very useful)
• Use the Immediate window (VBA) 

Point 2: « there seems to be inconsistencies in how vba is handling [the For Each control variable] »

No, VBA is consistent in requiring that the control variable for a For Each loop (colRange, in this case) be a Variant or an Object. If you needed colRange to be a String (you don't; VBA will happily use a string value inside a Variant), you would instead store the result of the Split function into a defined variable (e.g., Dim TargetRanges as String() ) and use a standard For loop (with a range of LBound(TargetRanges) To UBound(TargetRanges) ).

But as noted under Point 1, that's an inappropriate loop. And by now you probably realize that you did not need to put complete range identifiers into targetCols. (As long as the target rows are the same rows as the master rows, you don't need GetSelectedRanges to specify build range identifiers at all. Only if the rows were different between the two would you need to specify the starting row in that procedure, and more code would be required in CopyData to advance through the target range.)

Point 3: Your code for validating user-input column letters requires that they enter an upper-case letter. It's better design to accept any-case letters, and use the UCase$ function on their input as needed.

Point 4: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook modules, worksheet modules, UserForm modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.