在我的 VBA 代碼中，下面的目標是洗掉任何包含紅色字樣的單元格，只要紅色的左右兩邊都有一個空字串。所以“紅色”和“深紅色”都將被洗掉。現在我的代碼導致編譯錯誤，我不知道如何修復，因為我在 VBA 代碼方面沒有很多經驗。
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
uj5u.com熱心網友回復：
如果我正確理解了您的意圖，我相信這可能會為您解決問題。
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 color
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
uj5u.com熱心網友回復：
你會得到一個編譯錯誤，因為Color它實際上是stdole庫中的一個常量。
無法分配此常量，因此出現錯誤。
可以使用區域變數，例如Dim Color As Variant，盡管我不明白For Each回圈的意義是什么，因為您實際上從未Color在其中使用過。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/460535.html