Solved: Sumif Concatenate VBA code

[Xrull]

Hello,
I'm trying to improve on a code. This code worked with one column

Sub Macro1()

With Sheets("Result")
    iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    For i = 3 To iLastRow
.Cells(i, "C").Value = Evaluate("=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & "C$1,Data!$C$2:$C$9)")
'
Next i
End With
End Sub

, but I'm having a difficult time (better yet, no idea) including the column.
I posted a the formula similar to this one on another forum a few weeks ago:

=SUMIF(Data!$B$2:$B$9,Result!$B3&Result!C$1,Data!$C$2:$C$9)

and this is the code I conjured up:

Sub Macro1()

With Sheets("Result")
    iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    For i = 3 To iLastRow 
With Sheets("Result")
    aLastColumn = .Cells(.Columns.Count, "1").End(xlRight).Column
    For a = 2 To aLastColumn
    
.Cells(i, "C").Value = Evaluate("=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & a & "1,Data!$C$2:$C$9)")
'
Next i
Next a
End With
End With
End Sub

But I'm far from being a wizard, and it failed.
Can someone provide me with a solution, and tell me what is wrong with my logic?
How long will it take so this icon won't be my mantra any more?

Thanks,
Xrull

[mdmackillop]

The formula may not be quite right but try this.
[vba]Sub Macro1()
With Sheets("Result")
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To iLastRow
With Sheets("Result")
aLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
For a = 2 To aLastColumn
.Cells(i, "C").Value = Evaluate("=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, "a").Address & ",Data!$C$2:$C$9)")
Next a
End With
Next i
End With
End Sub[/vba]

When your working with complicated formulae, try writing the formula text into a cell. It will show if you've got it right e.g
[vba]
.Cells(i, "C").Value = "SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, "a").Address & "1,Data!$C$2:$C$9)"

[/vba]

[Xrull]

mdmackillop
I tried the code, but it is giving me zeros in the first column.
I've attached the file.
Thanks,
Xrull

[mdmackillop]

Use this line to insert the formula. That will show the error
[VBA]
.Cells(i, "C").Formula = "=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, "a").Address & ",Data!$C$2:$C$9)"
[/VBA]

[Bob Phillips]

[vba]

Sub Macro1()
With Sheets("Result")

iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To iLastRow

With Sheets("Result")

aLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
For a = 3 To aLastColumn

.Cells(i, a).Value = Evaluate("=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, a).Address & ",Data!$C$2:$C$9)")
Next a
End With
Next i
End With
End Sub
[/vba]

[mdmackillop]

[VBA] Sub Macro1()
With Sheets("Result")
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 3 To iLastRow
With Sheets("Result")
aLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
For a = 3 To aLastColumn
.Cells(i, a).Formula = "=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, a).Address & ",Data!$C$2:$C$9)"
Next a
End With
Next i
End With
End Sub
[/VBA]

[Xrull]

xld,
The code worked.
Can you explain to me why I have to use the ".address"? What was wrong with my method?
Thank you very much,
xdl and mdmackillop

[Bob Phillips]

You ave to use Address, as the code moves along columns, and your code had B hard-coded in, so it didn't adjust to the columns.

But, you also started from the wrong column (2 not 3) and using Cells(1, "a") instead of Cells(1, a), that is, use the variable.

[Xrull]

Me again,
I don't know if I should have created another post or I could add this question since it is related to my original post.
But I tried adjusting the formula by doing this:

Sub Macro2()
    With Sheets("Result")
        iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = 3 To iLastRow
            With Sheets("Result")
                aLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
                For a = 3 To aLastColumn
                    .Cells(i, a).Value = Evaluate("=SUM(SUMIF(Data1!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, a).Address & ",Data1!$C$2:$C$9), SUMIF(Data2!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, a).Address & ",Data2!$C$2:$C$9))")
                Next a
            End With
        Next i
    End With
End Sub

But it doesn't work with large data. Is there something I'm leaving out?
Thanks,
Xrull

[mdmackillop]

I don't see a problem with the basic code. Can you post your workbook?

[Xrull]

mdmckillop,
I've attached the file. When I use the formula, it is correct, but when I press the button and run the code, it is out. The original sheet has much more data, but I would not be able to upload a file similar to the original.
Thanks again,
Xrull

[Xrull]

I found the error. I rebuilt the file. I got my ranges right.
Thanks,
Xrull