VBA code variable required - cant assign to this expression-CodePudding

In my VBA code the goal below is to delete any cell that has the word red in it as long as there are a empty string to the left and right of red. So "red" and "dark red" would both be deleted. Right now my code is causing a compile error and I dont know how to fix because I dont have a lot of experience in VBA code.

 Sub collapse_columns()
    Dim x As Integer
    For x = 1 To 4
        collapse_column x
    Next
End Sub



Sub collapse_column(column_number As Integer)

    Dim row As Long
    Dim s As Worksheet
    Dim last_row As Long
    Set s = ActiveSheet ' work on the active sheet
    'Set s = Worksheets("Sheet1") 'work on a specific sheet
    
    last_row = ActiveSheet.Cells(s.Rows.Count, column_number).End(xlUp).row
    
    Dim colors_to_delete As String
    colors_to_delete = "red"
    
    For row = last_row To 1 Step -1
         For Each Color In Split(Cells(row, column_number).Value, " ")
              If InStr(1, Cells(row, column_number).Value, colors_to_delete) > 0 Then
                   Cells(row, column_number).Delete xlUp
                   Exit For
         Next Color
    Next row
    
End Sub

CodePudding user response：

If I understood your intention correctly, I believe this might do the trick for you.

Sub collapse_columns()
Dim x As Integer
For x = 1 To 4
    collapse_column x
Next
End Sub

Sub collapse_column(column_number As Integer)

Dim row As Long
Dim s As Worksheet
Dim last_row As Long
Set s = ActiveSheet ' work on the active sheet
'Set s = Worksheets("Sheet1") 'work on a specific sheet

last_row = s.Cells(s.Rows.Count, column_number).End(xlUp).row

Dim colors_to_delete As String: colors_to_delete = "red"

For row = last_row To 1 Step -1

    If InStr(1, " " & s.Cells(row, column_number).Value & " ", " " & colors_to_delete & " ") > 0 Then
    
        s.Cells(row, column_number).Delete xlUp
        
    End If

Next row

End Sub

CodePudding user response：

You get a compile error because Color is actually a constant from the stdole library.

This constant cannot be assigned to, hence the error.

A local variable, e.g. Dim Color As Variant, can be used, though I don't see what the point of the For Each loop is, since you never actually use Color within it.