It's my first time doing VBA Macro and I'm having a hard time understanding the problem.
I'm trying to filter and color cells with specific values but when I try running the code it says 'Type mismatch'.
Dim count, i As Long
Dim ws As Worksheet
Dim count, i As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
count = ws.Cells(Rows.count, "E").End(xlUp).Row
i = 2
Do While i <= count
If Cells(i, 5).Value = "#N/A" _
Or Cells(i, 5).Value = "#Ref" _
Or Cells(i, 5).Value = "Null" _
Or Cells(i, 5).Value = "" _
Or Cells(i, 5).Value = "#DIV/0!" _
Or Cells(i, 5).Value = "" _
Or Cells(i, 5).Value Like "*-*" Then
Cells(i, 5).Interior.Color = RGB(38, 201, 218)
End If
i = i 1
Loop
ws.Range("E1").AutoFilter Field:=5, Criteria1:=RGB(38, 201, 218), Operator:=xlFilterCellColor
And when I click the debug it highlights the If statements. Is there a way to solve this or is there a better way to filter these values while highlighting them in VBA?
CodePudding user response：
Not really an answer, more of a expanded comment.
If IsError(Cells(i, 5)) Then
Cells(i, 5).Interior.Color = RGB(0, 0, 255)
ElseIf Cells(i, 5).Value = "" Then
Cells(i, 5).Interior.Color = RGB(0, 0, 255)
Else
Cells(i, 5).Interior.Color = xlNone
End If
Also, this to sift the errors https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/cell-error-values
CodePudding user response：
First problem: If your cell contain an error, it doesn't contain the string "#N/A" or "#Ref", it contains a special value. What you see is only a visual representation of that error. If you want to check for an error within Excel, you should use the function IsError. That would lead to (wait, don't use that!):
If isError(Cells(i, 5).Value)
Or Cells(i, 5).Value = "Null" _
Or Cells(i, 5).Value = "" _
Or Cells(i, 5).Value Like "*-*" Then
Second problem: In VBA, there is no optimization for a conditional statement, VBA will always evaluate all parts. Your If-statement contains several conditions, combined with Or. While other programming languages quit evaluating when one condition makes the whole expression true, VBA will continue to evaluate all conditions.
Now if you have an error in a cell and you would use the code above, you will still get a type mismatch error: You cannot compare an error with a string. The condition isError(Cells(i, 5).Value) will get True, but VBA will continue to compare the cell content with strings and that gives you the mismatch. You need a way to split your If-statement.
Some more remarks: You are assigning the worksheet you want to work with to variable ws, but you are not using it. You will need to qualify every single usage of Cells (write ws.Cells(i, 5), else VBA will assume you are working with the Active Sheet, and that may or may not be Sheet1. Usually, this is done with a With-statement (note all the leading dots).
Your declaration statement is flawed (a common mistake in VBA), you will need to specify the type for every variable. In your case, Count will be of type Variant, not Long. No problem here, but in other cases it is, so make it a habit to declare all variables correctly.
You should use a For-Loop rather than a Do While.
Dim count As Long, i As Long
With ws
count = .Cells(.Rows.count, "E").End(xlUp).Row
For i = 2 to count
Dim markCell as boolean
If isError(.Cells(i, 5).Value) Then
markCell = True
ElseIf .Cells(i, 5) = "Null" _
Or .Cells(i, 5).Value = "" _
Or .Cells(i, 5).Value Like "*-*" Then
markCell = True
Else
markCell = False
End If
If markCell Then
.Cells(i, 5).Interior.Color = RGB(38, 201, 218)
End If
Next i
End With
CodePudding user response：
If you want to check for specific errors you first need to check if there are errors with IsError. You cannot check for an error and a value in one condition:
Do While i <= count
Dim Condition As Boolean
Condition = False ' initialize when in a loop!
If IsError(Cells(i, 5).Value) Then
If Cells(i, 5).Value = CVErr(xlErrNA) _
Or Cells(i, 5).Value = CVErr(xlErrRef) _
Or Cells(i, 5).Value = CVErr(xlErrNull) _
Or Cells(i, 5).Value = CVErr(xlErrDiv0) Then
Condition = True
End If
ElseIf Cells(i, 5).Value = "" Or Cells(i, 5).Value Like "*-*" Then
Condition = True
End If
If Condition = True Then
Cells(i, 5).Interior.Color = RGB(38, 201, 218)
End If
Loop
CodePudding user response：
Filter By Color
Sub FilterByColor()
Const wsName As String = "Sheet1"
Const Col As String = "E"
Dim FilterColor As Long: FilterColor = RGB(38, 201, 218)
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
If ws.FilterMode Then ws.ShowAllData ' remove any filters
Dim lRow As Long: lRow = ws.Cells(ws.Rows.count, Col).End(xlUp).Row
Dim rgData As Range ' data range; no header
Set rgData = ws.Range(ws.Cells(2, Col), ws.Cells(lRow, Col))
rgData.Interior.Color = xlNone ' remove all colors
Dim rgColor As Range ' the combined range to be colored
Dim DataCell As Range ' each cell of the data range
Dim cString As String
Dim DoColor As Boolean
For Each DataCell In rgData.Cells
If IsError(DataCell) Then ' error value
DoColor = True
Else
cString = CStr(DataCell.Value)
If Len(cString) = 0 Then ' blank
DoColor = True
Else
If InStr(1, cString, "-") > 0 Then ' contains a minus ('-')
DoColor = True
End If
End If
End If
If DoColor Then
If rgColor Is Nothing Then ' combine cells into a range
Set rgColor = DataCell
Else
Set rgColor = Union(rgColor, DataCell)
End If
DoColor = False ' don't forget to reset
End If
Next DataCell
If rgColor Is Nothing Then Exit Sub
rgColor.Interior.Color = FilterColor ' apply color in one go
Dim rgTable As Range ' table range; header included
Set rgTable = ws.Range(ws.Cells(1, Col), ws.Cells(lRow, Col))
rgTable.AutoFilter 1, FilterColor, xlFilterCellColor
' To delete the rows, you could continue with e.g.:
' rgData.SpecialCells(xlCellTypeVisible).EntireRow.Delete
' ws.AutoFilterMode = False ' remove 'AutoFilter'
End Sub