• 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 trying to muliply column h and column i and paste it in column T.

Below is the code i am using but giving me Run time Error 13 Type mismatch error. What may cause this error?

Code: 
Worksheets("Source Data").Range("T2", Cells(Rows.Count, "T").End(xlUp)).Formula = Worksheets("Source Data").Range("H1") * "100%" / Worksheets("Source Data").Range("I1") + "100%"
 
I tried below code but still gettingf Object required Error.

Code: 
Private Sub Looping()
Dim w As Worksheet
Dim LastRow As Long
Dim X As Integer
Dim Y As Integer
Dim Z As Range
Set w = Sheets("Sheet1")
LastRow = Cells(Rows.Count, "H").End(xlUp).Row
Set X = Evaluate("H2:H" & LastRow & "*1")
Set Y = Evaluate("I2:I" & LastRow & "+1")
Set Z = Range("T2:T" & LastRow)
Z = X / Y
End Sub

vletm said:
Dokat "100%"
What would give formula = H1 * "100%" / I1 + "100%"?
notes: "100%" is text and even 100% = 1
Click to expand...
 
Last edited:
I have a range starting from H2 to H98xx

I have two columns
Column H: Base Dollar Sales This Year
Column I: Base Dollar Sales % Chg YA

I am trying to calculate Base Dollar Sales Last Year by multiplying all the values in column H by 1 and diving by (1 plus the value in column I) and paste it to column T.

In other words
Column H: $10,713,008
Column I: 46.4%
Column T: 10,713,008 x (1+46.4%)/1

I hope this makes sense.

Thanks

vletm said:
Dokat
Could You try to write: What would You try to do?
Click to expand...
 
Dokat
I would do it as below.
Change ActiveSheet as Your used Sheet
Code: 
Sub Do_It()
    Application.ScreenUpdating = False
    With ActiveSheet
        For h = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
            .Cells(h, 20) = .Cells(h, 8) * (1 + .Cells(h, 9) / 1)
        Next h
    End With
    Application.ScreenUpdating = True
End Sub
... and if there are other 'formulas' then control calculation!
 
This worked.
Thank you for your help


vletm said:
Dokat
I would do it as below.
Change ActiveSheet as Your used Sheet
Code: 
Sub Do_It()
    Application.ScreenUpdating = False
    With ActiveSheet
        For h = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
            .Cells(h, 20) = .Cells(h, 8) * (1 + .Cells(h, 9) / 1)
        Next h
    End With
    Application.ScreenUpdating = True
End Sub
... and if there are other 'formulas' then control calculation!
Click to expand...
 
Its taking too long to run the code is there a way to speed it up?

vletm said:
Dokat
I would do it as below.
Change ActiveSheet as Your used Sheet
Code: 
Sub Do_It()
    Application.ScreenUpdating = False
    With ActiveSheet
        For h = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
            .Cells(h, 20) = .Cells(h, 8) * (1 + .Cells(h, 9) / 1)
        Next h
    End With
    Application.ScreenUpdating = True
End Sub
... and if there are other 'formulas' then control calculation!
Click to expand...
 
as written:
if there are other 'formulas' then control calculation!
= set calculation to manual

How long time would be 'too long time'?
Have You tried to do it manually?
 
Yes, there are other formula. It took 30 mins and i abort it.

Do i set it manual on the top or the bottom of the code. Below is the entire code.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:AB")) Is Nothing Then
Call Module1.UpdateValueStates
Call Module3.UpdateValueCategory
Call Module5.LeftRight
Call Module4.TransposeDates
Worksheets("Source Data").Range("B:B").Copy Range("AP:AP")
Worksheets("Source Data").Range("AP:AP").RemoveDuplicates Columns:=Array(1), Header:=xlYes
Call Module2.UpdateValueTime
Worksheets("Source Data").Range("AQ2", Cells(Rows.Count, "AQ").End(xlUp)).Copy
Worksheets("Source Data").Range("AY2").PasteSpecial Transpose:=True
Call Module6.Combine
Call Module7.UpdateCatSegment
Call Module8.UpdateBrand
Application.ScreenUpdating = False
With Sheets("Source Data")
For h = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
.Cells(h, 20) = .Cells(h, 8) / (1 + .Cells(h, 9))
Next h
End With
Application.ScreenUpdating = True
End If
End Sub

vletm said:
as written:
if there are other 'formulas' then control calculation!
= set calculation to manual

How long time would be 'too long time'?
Have You tried to do it manually?
Click to expand...
 
When i set to manual calculation. Excel freezes and crashes.

here is the code i am using
Code: 
Application.Calculation=xlManual
Application.Calculation = xlAutomatic
vletm said:
as written:
if there are other 'formulas' then control calculation!
= set calculation to manual

How long time would be 'too long time'?
Have You tried to do it manually?
Click to expand...
 
Dokat
Yes, there are other formula. It took 30 mins and i abort it.
...
Your the newest code ... Oh Yeah!
I've written 'only' that short part!
...
You've wondered one line and You have there ... some calls
and the 'best part', that it's in Worksheet_Change!
...
Are You sure that Your code do not go to almost endless loop?
= are You 99% sure that none of Your code
do not trigger again... again ... Worksheet_Change?
... or anyway, it would take a long ... long time to finish!
Have You tried to run Your code 'line-by-line'?

I won't use that kind of code-lines with Worksheet_Change !
or at least, I would used code as below, within If ... EndIf:

Code: 
Sub Do_it()
    With Application
        CM = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

'   Your calculations

    With Application
        .Calculation = CM
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

PS. I'm 99% sure that, You do not need to update ~9800rows of data after every change!
 
Last edited:
Thank you for your response. I modified the code. Please see below but now getting Compile Error: For without Next error.
Can you please advise
Thanks

Code: 
Sub Do_it()
    With Application
        CM = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    With Sheets("Source Data")
        For h = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
            For i = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row
                .Cells(h, 20) = .Cells(h, 8) / (1 + .Cells(i, 8))
            Next i
'    missed next lines
        next h
    end with
'    no need two times next
'    Application.ScreenUpdating = True
    With Application
        .Calculation = CM
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub


vletm said:
Dokat
Yes, there are other formula. It took 30 mins and i abort it.
...
Your the newest code ... Oh Yeah!
I've written 'only' that short part!
...
You've wondered one line and You have there ... some calls
and the 'best part', that it's in Worksheet_Change!
...
Are You sure that Your code do not go to almost endless loop?
= are You 99% sure that none of Your code
do not trigger again... again ... Worksheet_Change?
... or anyway, it would take a long ... long time to finish!
Have You tried to run Your code 'line-by-line'?

I won't use that kind of code-lines with Worksheet_Change !
or at least, I would used code as below, within If ... EndIf:

Code: 
Sub Do_it()
    With Application
        CM = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

'   Your calculations

    With Application
        .Calculation = CM
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

PS. I'm 99% sure that, You do not need to update ~9800rows of data after every change!
Click to expand...
 
Last edited by a moderator:
Dokat
I edited Your previous code ...
Hint1: every With ... would also have End With
Hint2: every For h= ... would also have Next h
 
You must log in or register to reply here.
Back
Top