CODEWITHSUNDEEP
vbaexcelif-statement
cthallofamer
cthallofamer

Reputation: 141

How to place IF statement in next available column in Excel VBA?

I am trying to place an IF statement in the next available column(dynamic) in row 2. Once the IF statement is in that cell, I want to add in the value from the last used row(dynamic) from that same column. I have been having trouble coding the IF statement into VBA. I also need help with coding it so it adds in the last row from the column into the cell with the IF statement. Here is my code so far: 

Sub vlookupF5()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet

Dim NextColumn As Long
Dim ColumnStr As String
Dim ColumnStr2 As String
Dim ColumnStr3 As String
Set sourceSheet = Worksheets("Actuals")
Set outputSheet = Worksheets("Pivot")
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With outputSheet
    NextColumn = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
    If NextColumn + 1 < 27 Then
        ColumnStr = Chr(64 + NextColumn + 1)
    Else
        ColumnStr = Chr(64 + Int((NextColumn + 1) / 26)) & Chr(64 + ((NextColumn + 1) Mod 26))
    End If
    If NextColumn - 1 < 27 Then
        ColumnStr3 = Chr(64 + NextColumn - 1)
    Else
        ColumnStr3 = Chr(64 + Int((NextColumn - 1) / 26)) & Chr(64 + ((NextColumn - 1) Mod 26))
    End If
    If NextColumn < 27 Then
        ColumnStr2 = Chr(64 + NextColumn)
    Else
        ColumnStr2 = Chr(64 + Int((NextColumn) / 26)) & Chr(64 + ((NextColumn) Mod 26))
    End If
    OutputLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row - 1
    Worksheets("Pivot").Activate
    .Range(Cells(2, NextColumn), .Cells(2, NextColumn)).Formula = _
    "=IF(R3C5=""Feb"",Actuals!R133C22,IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600),IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24),IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25),IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26),IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27),IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28),IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29),IF(R3C5=""Oct"",SUM" & _
                                                                  "!R133C22:R133C30),IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31),IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32),"""")))))))))))" * .Cells(OutputLastRow, NextColumn)
End With
End Sub

I keep getting

"Error 13: Type Mismatch"

for this line:

"=IF(R3C5=""Feb"",Actuals!R133C22,IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600),IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24),IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25),IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26),IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27),IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28),IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29),IF(R3C5=""Oct"",SUM" & _
            "!R133C22:R133C30),IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31),IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32),"""")))))))))))" + .Cells(OutputLastRow, NextColumn)

Can anyone help??

Upvotes: 1

Views: 155

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You're trying to add a numeric value to a a string literal, that won't work: 

.Range(Cells(2, NextColumn), .Cells(2, NextColumn)).Formula = _
    "=IF(R3C5=""Feb"",Actuals!R133C22,IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600),IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24),IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25),IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26),IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27),IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28),IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29),IF(R3C5=""Oct"",SUM" & _
                                                                  "!R133C22:R133C30),IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31),IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32),"""")))))))))))" + .Cells(OutputLastRow, NextColumn)

Let's break this down:

Dim sLongFormula$
sLongFormula = "=IF(R3C5=""Feb"",Actuals!R133C22,IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600),IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24),IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25),IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26),IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27),IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28),IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29),IF(R3C5=""Oct"",SUM" & _
                                                                  "!R133C22:R133C30),IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31),IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32),"""")))))))))))"

So what you end up with is:

.Range(Cells(2, NextColumn), .Cells(2, NextColumn)).Formula = sLongFormula  + .Cells(OutputLastRow, NextColumn)

You simply can't add numeric values to a string in VBA (using the & concatenator instead of the + would do an implicit type-conversion of numeric => string, however).

What you really need, I think:

.Range(.Cells(2, NextColumn), .Cells(2, NextColumn)).Formula = _
    sLongFormula & "+" & .Cells(OutputLastRow, NextColumn)

Note the addition of a leading . in .Cells(2, NextColumn) Previously, you would get a 1004 because you just Activated the pivot sheet, but you're defining a range inside a With block, and it is not possible to construct a range that includes cells spanning multiple worksheets.

__

Your formula looks suspect, if you break it down with more line breaks, you have:

sLongFormula = "=IF(R3C5=""Feb"",Actuals!R133C22,
            IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600), _
            IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24), _
            IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25), _
            IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26), _
            IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27), _
            IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28), _
            IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29), _
            IF(R3C5=""Oct"",SUM" & _
                                    "!R133C22:R133C30), _
            IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31), _
            IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32), _
            """")))))))))))"

So you're missing an opening parens and the Actuals for the Oct condition:

sLongFormula = "=IF(R3C5=""Feb"",Actuals!R133C22,
            IF(R3C5=""Mar"",SUM(Actuals!R133C22:R133C600), _
            IF(R3C5=""Apr"",SUM(Actuals!R133C22:R133C24), _
            IF(R3C5=""May"",SUM(Actuals!R133C22:R133C25), _
            IF(R3C5=""Jun"",SUM(Actuals!R133C22:R133C26), _
            IF(R3C5=""Jul"",SUM(Actuals!R133C22:R133C27), _
            IF(R3C5=""Aug"",SUM(Actuals!R133C22:R133C28), _
            IF(R3C5=""sep"",SUM(Actuals!R133C22:R133C29), _
            IF(R3C5=""Oct"",SUM(Actuals!R133C22:R133C30), _
            IF(R3C5=""Nov"",SUM(Actuals!R133C22:R133C31), _
            IF(R3C5=""Dec"",SUM(Actuals!R133C22:R133C32), _
            """")))))))))))"

I also notice that March is C600, but all the other months are indexed from 24, etc. And strangely, February is not a Sum function at all. Both of these quirks might be correct for your usage, but they appear to me inconsistent.

You can probably simplify that monstrous function using some sort of lookup or SumIf/SumIfs function, too.

Upvotes: 1

Related Questions