CODEWITHSUNDEEP
vbaexcel
Davis Vance
Davis Vance

Reputation: 85

Type mismatch (Error 13) in Loop

Line 4 is messing my loop up with a type mismatch! What am I doing wrong?

For i = 4 To 8
    j = 20 + i
    Col = Columns(j)
    Range("'" & Col & "3'").FormulaR1C1 = "=IF(RC[-11]=0,0,(IF(SUMIF(R3C2:R" & lRow & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")>RC[-11]*1000000, SUMIF(R3C2:R" & lRow1 & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")- RC[-11]*1000000,0)))"
Next i

Upvotes: 0

Views: 93

Answers (2)

A.S.H
A.S.H

Reputation: 29332

You're doing many errors.

First, col is a column, not a (range address) string. You cannot concatenate a column to a string.

Second, you should not enclose a range address with single-quotes (').

What you probably wanted to do is:

Cells(3, j).Formula = ...

Upvotes: 2

CallumDA
CallumDA

Reputation: 12113

Try this:

For i = 4 To 8
    Cells(3, 20 + i).FormulaR1C1 = "=IF(RC[-11]=0,0,(IF(SUMIF(R3C2:R" & lRow & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")>RC[-11]*1000000, SUMIF(R3C2:R" & lRow1 & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")- RC[-11]*1000000,0)))"
Next i

By using Cells or Range on their own Excel will assume you want to reference the active worksheet, in the active workbook. It's a much better idea to specify exactly which workbook/ worksheet you want the code to run on. E.g.:

For i = 4 To 8
    ThisWorkbook.Worksheets("Sheet1").Cells(3, 20 + i).FormulaR1C1 = "=IF(RC[-11]=0,0,(IF(SUMIF(R3C2:R" & lRow & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")>RC[-11]*1000000, SUMIF(R3C2:R" & lRow1 & "C2, RC2,R3C" & i & ":R" & lRow & "C" & i & ")- RC[-11]*1000000,0)))"
Next i

Upvotes: 2

Related Questions