• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run Time Error 13 Type Mismatch Error

D

Dokat

Member
Hi,

I am getting Run Time Error 13 Type Mismatch error when running the below code. Can you pls help solving the issue,

This line is highlighted in yellow.
Code: 
 .Cells(i, 13).Value = .Cells(i, 11).Value - .Cells(i, 12).Value


Code: 
Sub MathLWBRANDINS()

    Dim i As Integer
    Dim condition As Range
   
    Application.ScreenUpdating = False

    With Sheets("INS")
       
        For i = 71 To 90
            .Cells(i, 9).Value = .Cells(i, 7).Value - .Cells(i, 8).Value

            If .Cells(i, 8).Value <> 0 Then
            .Cells(i, 10).Value = .Cells(i, 7).Value / .Cells(i, 8).Value - 1
           
            End If
         
            If .Range("G71").Value <> 0 Then
            .Cells(i, 11).Value = .Cells(i, 7).Value / .Range("G71").Value * 100
           
            End If
       
            If .Range("H71").Value <> 0 Then
            .Cells(i, 12).Value = .Cells(i, 8).Value / .Range("H71").Value * 100
           
            End If
            .Cells(i, 13).Value = .Cells(i, 11).Value - .Cells(i, 12).Value
                     
       
        Next i
    End With
   
    Application.ScreenUpdating = True
End Sub
 
Hi ,

Going by only what you have posted , see if this works :
Code: 
Sub MathLWBRANDINS()
    Dim i As Integer
    Dim condition As Range
 
    Application.ScreenUpdating = False

    With Sheets("INS")
        For i = 71 To 90
            .Cells(i, 9).Value = .Cells(i, 7).Value - .Cells(i, 8).Value

            If .Cells(i, 8).Value <> 0 Then
                .Cells(i, 10).Value = .Cells(i, 7).Value / .Cells(i, 8).Value - 1
            End If
       
            If ((.Range("G71").Value <> 0) And (.Range("H71").Value <> 0)) Then
              .Cells(i, 11).Value = .Cells(i, 7).Value / .Range("G71").Value * 100
              .Cells(i, 12).Value = .Cells(i, 8).Value / .Range("H71").Value * 100
              .Cells(i, 13).Value = .Cells(i, 11).Value - .Cells(i, 12).Value
            End If
        Next i
    End With
 
    Application.ScreenUpdating = True
End Sub
Narayan
 
I modified the code but now getting Compile Error end with without with error message

Code: 
    Dim i As Integer, x As Integer, z As Integer, h As Integer, f As Integer, t As Integer
    Dim condition As Range
   
    Application.ScreenUpdating = False

    With Sheets("INS")
   
       
        For i = 152 To 173
       
            .Cells(i, 9).Value = .Cells(i, 7).Value - .Cells(i, 8).Value

            If .Cells(i, 8).Value <> 0 Then
                .Cells(i, 10).Value = .Cells(i, 7).Value / .Cells(i, 8).Value - 1
            End If
         
            If .Range("G153").Value <> 0 Then
                .Cells(i, 11).Value = .Cells(i, 7).Value / .Range("G153").Value * 100
            If .Range("H153").Value <> 0 Then
                .Cells(i, 12).Value = .Cells(i, 8).Value / .Range("H153").Value * 100
                .Cells(i, 13).Value = .Cells(i, 11).Value - .Cells(i, 12).Value
        End If
        For x = 174 To 181
         
               .Cells(x, 9).Value = .Cells(x, 7).Value - .Cells(x, 8).Value

            If .Cells(x, 8).Value <> 0 Then
                .Cells(x, 10).Value = .Cells(x, 7).Value / .Cells(x, 8).Value - 1
            End If
         
            If .Range("G174").Value <> 0 Then
                .Cells(x, 11).Value = .Cells(x, 7).Value / .Range("G174").Value * 100
            End If
       
            If .Range("H174").Value <> 0 Then
                .Cells(x, 12).Value = .Cells(x, 8).Value / .Range("H174").Value * 100
            End If
       
            .Cells(x, 13).Value = .Cells(x, 11).Value - .Cells(x, 12).Value
            Next
        For z = 182 To 195
         
            .Cells(z, 9).Value = .Cells(z, 7).Value - .Cells(z, 8).Value

            If .Cells(z, 8).Value <> 0 Then
                .Cells(z, 10).Value = .Cells(z, 7).Value / .Cells(z, 8).Value - 1
            End If
         
            If .Range("G182").Value <> 0 Then
                .Cells(z, 11).Value = .Cells(z, 7).Value / .Range("G182").Value * 100
            End If
       
            If .Range("H182").Value <> 0 Then
                .Cells(z, 12).Value = .Cells(z, 8).Value / .Range("H182").Value * 100
     
                .Cells(z, 13).Value = .Cells(z, 11).Value - .Cells(z, 12).Value
         
         
        For h = 196 To 197
         
            .Cells(h, 9).Value = .Cells(h, 7).Value - .Cells(h, 8).Value

            If .Cells(h, 8).Value <> 0 Then
                .Cells(h, 10).Value = .Cells(h, 7).Value / .Cells(h, 8).Value - 1
            End If
         
            If .Range("G196").Value <> 0 Then
                .Cells(h, 11).Value = .Cells(h, 7).Value / .Range("G196").Value * 100
            End If
       
            If .Range("H196").Value <> 0 Then
             .Cells(h, 12).Value = .Cells(h, 8).Value / .Range("H196").Value * 100
             .Cells(h, 13).Value = .Cells(h, 11).Value - .Cells(h, 12).Value
         
          End If
        Next h
    End With
   
    Application.ScreenUpdating = True
End Sub
 
Hi ,

Your post did not include the first line of your procedure ; my corrected version also does not have this. Please include it.
Code: 
    Dim i As Integer, x As Integer, z As Integer, h As Integer, f As Integer, t As Integer
    Dim condition As Range
 
    Application.ScreenUpdating = False

    With Sheets("INS")
        For i = 152 To 173
            .Cells(i, 9).Value = .Cells(i, 7).Value - .Cells(i, 8).Value

            If .Cells(i, 8).Value <> 0 Then .Cells(i, 10).Value = .Cells(i, 7).Value / .Cells(i, 8).Value - 1
       
            If .Range("G153").Value <> 0 Then .Cells(i, 11).Value = .Cells(i, 7).Value / .Range("G153").Value * 100
           
            If .Range("H153").Value <> 0 Then
                .Cells(i, 12).Value = .Cells(i, 8).Value / .Range("H153").Value * 100
                .Cells(i, 13).Value = .Cells(i, 11).Value - .Cells(i, 12).Value
            End If
        Next
       
        For x = 174 To 181
            .Cells(x, 9).Value = .Cells(x, 7).Value - .Cells(x, 8).Value

            If .Cells(x, 8).Value <> 0 Then .Cells(x, 10).Value = .Cells(x, 7).Value / .Cells(x, 8).Value - 1
       
            If .Range("G174").Value <> 0 Then .Cells(x, 11).Value = .Cells(x, 7).Value / .Range("G174").Value * 100
     
            If .Range("H174").Value <> 0 Then .Cells(x, 12).Value = .Cells(x, 8).Value / .Range("H174").Value * 100
     
            .Cells(x, 13).Value = .Cells(x, 11).Value - .Cells(x, 12).Value
        Next
       
        For z = 182 To 195
            .Cells(z, 9).Value = .Cells(z, 7).Value - .Cells(z, 8).Value

            If .Cells(z, 8).Value <> 0 Then .Cells(z, 10).Value = .Cells(z, 7).Value / .Cells(z, 8).Value - 1
       
            If .Range("G182").Value <> 0 Then .Cells(z, 11).Value = .Cells(z, 7).Value / .Range("G182").Value * 100
     
            If .Range("H182").Value <> 0 Then
                .Cells(z, 12).Value = .Cells(z, 8).Value / .Range("H182").Value * 100
                .Cells(z, 13).Value = .Cells(z, 11).Value - .Cells(z, 12).Value
            End If
        Next
       
        For h = 196 To 197
            .Cells(h, 9).Value = .Cells(h, 7).Value - .Cells(h, 8).Value

            If .Cells(h, 8).Value <> 0 Then .Cells(h, 10).Value = .Cells(h, 7).Value / .Cells(h, 8).Value - 1
       
            If .Range("G196").Value <> 0 Then .Cells(h, 11).Value = .Cells(h, 7).Value / .Range("G196").Value * 100
     
            If .Range("H196").Value <> 0 Then
              .Cells(h, 12).Value = .Cells(h, 8).Value / .Range("H196").Value * 100
              .Cells(h, 13).Value = .Cells(h, 11).Value - .Cells(h, 12).Value
            End If
        Next h
    End With
 
    Application.ScreenUpdating = True
End Sub
Narayan
 
It's

Code: 
 .Cells(i, 9).Value = .Cells(i, 7).Value - .Cells(i, 8).Value
I uploaded the file. It's under Module 6
Thank you
 

Attachments

  • Test.xlsb
    930.6 KB · Views: 3
Hi ,

The basic problem is that your For ... Next loop includes all the header / title rows.

Use only those rows which have data in them , simple.

Introducing unnecessary IF statements is not the solution.

Narayan
 
Hi ,

If you see the following statement :
Code: 
        For i = 152 To 173
The rows from 152 through 155 are rows which have no usable data ; why are they a part of the For ... Next loop ? The loop should start from 156 instead.

Narayan
 
You must log in or register to reply here.
Back
Top