Type Mismatch

[Jason]

I'm sure there is a simple solution to this, but can't
figure it out. I am getting a type mismatch error (run
time error '13') in the following code. Can anyone tell
me what's causing this and how to fix it. Thanks.

Sub Test1()
Dim A As String, B As String, C As String, D As String
A = "Sales": B = "COGS": C = "Gross Margin": D = "Net
Income"
If ActiveCell.Offset(-1, 0).Value = A Or B Or C Or D And _
ActiveCell.Offset(1, 0).Value = A Or B Or C Or D Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-1, 0).Select

End Sub

 

[Steve Garman]

Those Ors aren't doing what you think they are.

I think you mean:
If (ActiveCell.Offset(-1, 0).Value = A _
or ActiveCell.Offset(-1, 0).Value = B _
or ActiveCell.Offset(-1, 0).Value = C _
or ActiveCell.Offset(-1, 0).Value = D) _
And (ActiveCell.Offset(1, 0).Value = A _
or ActiveCell.Offset(1, 0).Value = B _
or ActiveCell.Offset(1, 0).Value = C _
or ActiveCell.Offset(1, 0).Value = D) Then

'It's probably worth creating variables to hold
' ActiveCell.Offset(-1, 0).Value and
' ActiveCell.Offset(1, 0).Value to save typing

 

[JE McGimpsey]

The reason you're getting the type mismatch is that you're trying to
perform a logical OR on text values instead of boolean values
(True/False) or numbers.

If ActiveCell.Offset(-1, 0).Value = A Or B Or C Or D

is equivalent to

If ActiveCell.Offset(-1, 0).Value = (A Or B Or C Or D)

What you're after is more like

If (ActiveCell.Offset(-1, 0).Value = A) Or (ActiveCell.Offset(-1,
0).Value = B) Or (ActiveCell.Offset(-1, 0).Value = C) Or
(ActiveCell.Offset(-1, 0).Value = D)

one alternative:

Public Sub Test2()
Dim vArr As Variant
Dim i As Long
vArr = Array("Sales", "COGS", "Gross Margin", "Net Income")
With ActiveCell
For i = LBound(vArr) To UBound(vArr)
If .Offset(-1, 0).Value = vArr(i) Or _
.Offset(1, 0).Value = vArr(i) Then
.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Exit For
End If
Next i
End With
End Sub

 

[JE McGimpsey]

Another alternative:

Public Sub Test3()
Dim bDelete As Boolean
With ActiveCell
Select Case .Offset(-1, 0).Value
Case "Sales", "COGS", "Gross Margin", "Net Income"
bDelete = True
Case Else
End Select
Select Case .Offset(1, 0).Value
Case "Sales", "COGS", "Gross Margin", "Net Income"
bDelete = True
Case Else
End Select
If bDelete Then
.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
End With

 

[Alan Beban]

To get the OP's expressed desired result (although that might not be
what he really wanted), then in each of your solutions the
..EntireRow.Delete should be

..EntireRow.Select
Selection.Delete

The first line changes the ActiveCell to the first column (if it wasn't
already there).

Alan Beban

 

[JE McGimpsey]

True, I missed that. I have such a strong aversion to that additional
Selection, though, that I'd avoid it by changing

ActiveCell.Offset(-1, 0).Select

to

Cells(ActiveCell.Row - 1, 1).Select

or even

ActiveCell.EntireRow(0).Resize(1, 1).Select

 

[Alan Beban]

Re ActiveCell.EntireRow(0).Resize(1, 1).Select:

Odd that, within the With ActiveCell,

..EntireRow(0).Resize(1, 1).Select returns an Object required error.
What's going on with that?

Alan Beban

 

[JE McGimpsey]

As I understand it, the

With ActiveCell

creates a temporary object variable and sets it to the ActiveCell's
range. When the object's row is deleted, the object is destroyed, so
that a further reference to it gives you the Object required error.

It would be stranger if it worked - the whole point of using "With...End
With" is to avoid the overhead of establishing a new reference for the
object.


You can't usIn article <[email protected]>,