Getting a type mismatch error

Question

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

I do not think you can only paste the values when using Cut. Its an all or nothing issue. — Scott Craner
– Scott Craner, Commented May 23, 2016 at 18:22
Also as to the Mid, you can only do one cell at a time not a range of cells. — Scott Craner
– Scott Craner, Commented May 23, 2016 at 18:22
mid of 3 characters but you only test for 4? Edit: yea, as Scott says, you need Range("B" & k) — findwindow
– findwindow, Commented May 23, 2016 at 18:23

Scott Craner · Accepted Answer · 2016-05-23 18:37:01Z

3

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

edited May 23, 2016 at 18:37

answered May 23, 2016 at 18:28

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

10 Comments

Weasemunk Over a year ago

I believe this is incorrect. Let's say Nrow = 100 and the if is true for the first time at k = 94. Then, the ranges B95:D100 and R94:T94 will be populated, while B2:D94 will be deleted.

Weasemunk Over a year ago

It could work if you did not add the backwards iteration, but I think deleting with xlUp was not intended.

Scott Craner Over a year ago

@SeanScott yes it should have been xlshiftup. Fixed it, as to why iterate backwards. If we are iterating forward and we reach row 6 which is true, then we copy and remove the line and the data shifts up. what was row 7 is now row 6 and gets skipped when the for loop iterates.

Weasemunk Over a year ago

Though we cannot speculate on how the user is using this data, I believe your "data cleaning" approach would also benefit from changing the target indices, since we will end up with a bunch of blanks in the columns R:T. I will make an edit to your first solution.

Scott Craner Over a year ago

@SeanScott I feel as the OP only gave a small sliver of the code that I wanted to give the same output that their code would reproduce and not assume they want different.

|

Weasemunk · Accepted Answer · 2016-05-23 18:26:21Z

2

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.

edited May 23, 2016 at 18:26

answered May 23, 2016 at 18:24

Weasemunk

4554 silver badges16 bronze badges

Collectives™ on Stack Overflow

Getting a type mismatch error

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related