Excel VBA Type Mismatch with If function

Question

I am very new to VBA and have been teaching myself for the last week. I have taken on a task that is maybe a bit to complex for me.

I have a document with columns A - AE I need to go through this document and move information on to separate sheets, depending on what it is. I am now trying to use an IF statement that needs to match 2 requirements before it moves the information. I can get each individual requirement to work but not both together as keep getting a Type Mismatch error.

I have no idea what i am doing wrong. Any help will be much appreciated.

Sub copyrows()

    Dim Test As Range, Cell As Object

    Set Test = Range("G2:Z4000") 'Substitute with the range which includes your True/False values

    For Each Cell In Test

        If IsEmpty(Cell) Then
            Exit Sub
        End If

        If Cell.Value = "Refund" And "Commission" Then
            Cell.EntireRow.Copy
            Sheet3.Select 'Substitute with your sheet
            ActiveSheet.Range("A65536").End(xlUp).Select
            Selection.Offset(1, 0).Select
            ActiveSheet.Paste
        End If

    Next

End Sub

Why is Cell an Object? I'd make it a Range instead, because that's what you're using it as. While Object may work, Range is better I believe. — BruceWayne
– BruceWayne, Commented Jul 3, 2017 at 16:12

Lunster · Accepted Answer · 2017-07-03 15:57:40Z

3

If Cell.Value = "Refund" And "Commission" Then

Should instead read:

If Cell.Value = "Refund" Or Cell.Value = "Commission" Then

You have to be explicit with each condition separated by boolean operators like AND or OR.

answered Jul 3, 2017 at 15:57

Lunster

9246 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-07-03 17:08:05Z

2

The reason for your error is already mentioned in the answer above by @IanL

However, your code is far from being optimized.

You can replace your 5 lines:

 Cell.EntireRow.Copy
 Sheet3.Select 'Substitute with your sheet
 ActiveSheet.Range("A65536").End(xlUp).Select
 Selection.Offset(1, 0).Select
 ActiveSheet.Paste

With 1:

Cell.EntireRow.Copy Destination:=Sheet3.Range("A65536").End(xlUp).Offset(1)

Which is not using Select, ActiveSheet, or Selection, just using fully qualified Range object.

answered Jul 3, 2017 at 17:08

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

Excel VBA Type Mismatch with If function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related