VBA - Error checking cell value

Asked

Viewed 25 times

0

Good morning, I have the following code IF returns "incompatible type" error, anyone know the reason? I’m trying to validate row values with columns.

Sub DeleteColumnByHeader()

Dim aCell As Range
Dim bCell As Range

For Each aCell In Range("C5:UZ5")
    For Each bCell In Range("A1:A1044")
     If aCell.Value = bCell.Value Then cell.EntireColumn.Delete
    Next bCell
Next aCell

End Sub
excel vba excel-vba

  • Which is the line that returns the error?

1 answer

0

In fact the error itself is already talking: "Incompatible types". This means that the program cannot compare the two values to check if they are equal and also cannot convert one value from one type to the other type to try to compare.

To avoid the error try adding an extra check layer to check if the types are equal:

Sub DeleteColumnByHeader()

Dim aCell As Range
Dim bCell As Range

For Each aCell In Range("C5:UZ5")
    For Each bCell In Range("A1:A1044")
       IF VarType(aCell.Value) = VarType(bCell.Value) Then
          If aCell.Value = bCell.Value Then cell.EntireColumn.Delete
       End If
    Next bCell
Next aCell

End Sub

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.