CODEWITHSUNDEEP
excelruntime-errortype-mismatchvba
shhhhh
shhhhh

Reputation: 1

run-time error 13 type mismatch

I'm trying to run a script that inserts blank rows between existing rows depending on the cell value of a certain column.

So far I have this: 

Set currentcell = Sheets("L1f").Range("I2:I131")

Do While Not IsEmpty(currentcell)

    Set nextcell = ActiveCell.Offset(1, 0)

   ** If currentcell.Value = 1 Then
        nextcell.EntireRow.Insert

    ElseIf currentcell.Value = 2 Then
        nextcell.EntireRow.Insert
        nextcell.EntireRow.Insert

    ElseIf currentcell.Value = 3 Then
        nextcell.EntireRow.Insert
        nextcell.EntireRow.Insert
        nextcell.EntireRow.Insert

    End If
    Set currentcell = nextcell
Loop

And I'm getting a run-time error 13 type mismatch on the highlighted line. I've searched through this site and haven't found a fix, but I'm new so any help/direction would be great thanks!

Upvotes: 0

Views: 251

Answers (1)

Chel
Chel

Reputation: 2623

I guess this code came from elsewhere, and you tried to edit the Set currentcell line so it would be limited to L1f!I2:I131 instead of just wherever the selection is?

The problem is that when you set currentcell to multiple cells, its .Value becomes an array instead of a single value. So you need to set currentcell to just the starting cell in L1f!I2:I131, and introduce another test to see when it falls outside of that range:

Dim currentcell as Range
Dim endcell as Range
Set currentcell = [L1f!I2]
Set endcell = [L1f!I131]

Do While currentcell.Row <= endcell.Row

    Set nextcell = currentcell.Offset(1, 0)

    If IsNumeric(currentcell) Then

        If currentcell.Value = 1 Then
            nextcell.EntireRow.Insert

        ElseIf currentcell.Value = 2 Then
            nextcell.EntireRow.Insert
            nextcell.EntireRow.Insert

        ElseIf currentcell.Value = 3 Then
            nextcell.EntireRow.Insert
            nextcell.EntireRow.Insert
            nextcell.EntireRow.Insert

        End If

    End If

    Set currentcell = nextcell
Loop

Upvotes: 0

Related Questions