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

Copper Contributor

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).
 jazzyelsie_0-1715607522611.png

 

 jazzyelsie_1-1715607540689.png

 

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.: jazzyelsie_2-1715607714082.png

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

 

3 Replies

@jazzyelsie 

Maybe this change will help.

Inserted in the file.

If not, just ignore it :smile:.

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

@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:


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.