Type Mismatch

[Bafio]

Hello everyone,

I'm new on the forum and I'm looking for help on this code.
The debug told me there a Type Mismatch mistake, but I can't find out what's wrong on my macro code.


Here below you can find my code, and the line with the problem is underlyned and in a bigger font.



Sub drag_all_formulas()


Dim UltimaRiga As Integer
Dim NumeroMesi As Integer

UltimaRiga = Range("G10000").End(xlUp).row

Application.ScreenUpdating = False


For i = 10 To UltimaRiga + 10000

If (Cells(i, 7) = "swap") And (Cells(i, 3) <> "*") And (Cells(i + 1, 3) <> "*") Then

NumeroMesi = Month(Cells(i, 16)) - Month(Cells(i, 15)) + (Year(Cells(i, 16)) - Year(Cells(i, 15))) * 12 + 1


Sheets("support").Select
Range("CA3:DU3").Select
Selection.Resize(NumeroMesi).Copy




Sheets("blotter oil 1").Select
Cells(i + 1, 1).Select
Selection.Insert Shift:=xlDown


Application.DisplayAlerts = True

Range(Cells(i + 1, 29), Cells(i + NumeroMesi, 41)).Select
Selection.ClearContents

Range(Cells(i + 1, 44), Cells(i + NumeroMesi, 44)).Select
Selection.ClearContents

Range(Cells(i + 1, 46), Cells(i + NumeroMesi, 46)).Select
Selection.ClearContents



ActiveCell.EntireRow.Select
Selection.Resize(NumeroMesi).Group


ActiveSheet.Outline.ShowLevels RowLevels:=1

End If

Next i



'formule per le sei/sette celle divalorizzazione


Sheets("support").Select
Range("CQ2:CW2").Select
Selection.Copy
Sheets("blotter oil 1").Select


For i = 10 To UltimaRiga + 10000

If ((Cells(i, 14) <> "") And (Cells(i, 18) = "") And (Cells(i, 21) = "")) Then
Cells(i, 17).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End If

Next i

SendKeys ("{ESC}")



Range("A10000").End(xlUp).Select

Application.ScreenUpdating = True


End Sub

 

[JeanKim]

On a Immediate Window type : ?typename(Cells(i, 14)) You will see that datatype is not String.

 

[Eric W]

Welcome to the forum.

I suspect that at least one of those cells contains a formula, and the result of that formula is an error, which will cause the error message you're seeing. There are several ways to handle that. You could check using the IsError method. Or you could create an error handler:

On Error Goto Somewhere:

It all depends on how you want to handle the situation.

 

[Bafio]

First of all thanks to both of you, then:
- For Jean, I've typed it and it says it's a boolean, so what should I do to make it works?
- For Eric, I tried to type your code into my macro code, I didn't find how I should write it. Could you please write it for me?

Thanks guys

 

[MARK858]

@Bafio, what is the value of i (in the underlined line) when the code errors?

 

[Eric W]

The code depends on what you want to do. I'll paste 2 examples to look at, both of which just skip the row with the error. You might want to include the row with the error, but that's up to you. You should be able to adapt the code.

This one uses IsError:

Sub test1()

    For i = 10 To UltimaRiga + 10000

        If IsError(Cells(i, 14)) Or IsError(Cells(i, 18)) Or IsError(Cells(i, 21)) Then GoTo IterateI:
        
        If ((Cells(i, 14) <> "") And (Cells(i, 18) = "") And (Cells(i, 21) = "")) Then
            Cells(i, 17).Select
            Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
        
IterateI:
    Next i
    
End Sub

This one uses the error handler:

Sub test2()

    On Error GoTo Oops:
    
    For i = 10 To UltimaRiga + 10000
        
        If ((Cells(i, 14) <> "") And (Cells(i, 18) = "") And (Cells(i, 21) = "")) Then
            Cells(i, 17).Select
            Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
        
IterateI:
    Next i
    
    On Error GoTo 0
    
    SendKeys ("{ESC}")
    Range("A10000").End(xlUp).Select
    Application.ScreenUpdating = True
    Exit Sub
    
Oops:
    Resume IterateI:
    
End Sub

 

[Bafio]

Mark the value is 10115. I tried to Dim Last Row as Long instead of as integer, but it seems it doesn't have any effect

 

[MARK858]

Mark the value is 10115. I tried to Dim Last Row as Long instead of as integer, but it seems it doesn't have any effect

It wouldn't unless it was over 32767, which it shouldn't be as UltimaRiga + 10000 should be 20000 at worst (you should have it dimmed as Long anyway though, as there is no benefit having it as Integer on modern computers, in fact there is supposedly a slight disadvantage with Integer even for smaller numbers).

Seeing as you know what the cells involved are now, do you have (as Eric W suspects) an error in either N10115, R10115 or U10115? if not what is in the cells?

Run it with Eric W's error handling first to see if it solves the issue before answering the questions above though.