CODEWITHSUNDEEP
vbaexcel
shilohln
shilohln

Reputation: 81

Getting a type mismatch error

I am getting a run-time error 13 'Type Mismatch' on this chunk of code and I cannot figure out why. I have not used the Mid() function before and that is the line that throws the error. Beginner here, any and all help appreciated.

I am wanting that if line to check to see if the third character in a string equals 4. If you know of a better way I am open to that as well.

For k = 2 To NRow
    If Mid(SummarySheet.Range("B2:B" & k), 3, 3) = 4 Then
        SummarySheet.Range("B" & k & ":D" & k).Cut
        SummarySheet.Range("R" & k & ":T" & k).PasteSpecial xlPasteValues
    End If
Next

Upvotes: 0

Views: 73

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

I believe this is what you are trying to do:

You will need to iterate backwards through your data.

Mid only allows one cell at a time. And the third criterion is the length so it should be 1.

You can set the values then remove the data from the set.

For k = Nrow To 2 Step -1
    If Mid(SummarySheet.Range("B" & k), 3, 1) = "4" Then
        SummarySheet.Range("R" & k & ":T" & k).Value = SummarySheet.Range("B" & k & ":D" & k).Value
        SummarySheet.Range("B" & k & ":D" & k).Delete xlshiftUp
    End If
Next

Now if you only want to clear the values and keep a blank row in the data then use this:

For k = Nrow To 2 Step -1
    If Mid(SummarySheet.Range("B" & k), 3, 1) = "4" Then
        SummarySheet.Range("R" & k & ":T" & k).Value = SummarySheet.Range("B" & k & ":D" & k).Value
        SummarySheet.Range("B" & k & ":D" & k).Clear
    End If
Next

Upvotes: 3

Weasemunk
Weasemunk

Reputation: 455

Mid() is used to get a substring of a single text value. Since you are trying this on an array, it will not work. I'm not completely sure this is what you want, but this should not throw an error:

If Mid(SummarySheet.Range("B" & k).Value, 3, 1) = "4" Then

Notice the reference is for a single cell, and the "4" is quoted to show that it's a string. I changed your last argument to 1 because you are looking for a single character.

Upvotes: 2

Related Questions