PDA

View Full Version : Solved: Sumif Concatenate VBA code



Xrull
03-17-2009, 04:53 PM
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:dunno.
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?
:banghead:
Thanks,
Xrull

mdmackillop
03-17-2009, 05:39 PM
The formula may not be quite right but try this.
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

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

.Cells(i, "C").Value = "SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, "a").Address & "1,Data!$C$2:$C$9)"

Xrull
03-17-2009, 05:55 PM
mdmackillop
I tried the code, but it is giving me zeros in the first column.
I've attached the file.
Thanks,
Xrull

mdmackillop
03-17-2009, 06:01 PM
Use this line to insert the formula. That will show the error

.Cells(i, "C").Formula = "=SUMIF(Data!$B$2:$B$9,Result!$B" & i & "&Result!" & Cells(1, "a").Address & ",Data!$C$2:$C$9)"

Bob Phillips
03-17-2009, 06:04 PM
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

mdmackillop
03-17-2009, 06:07 PM
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

Xrull
03-17-2009, 06:21 PM
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
03-18-2009, 02:07 AM
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
03-18-2009, 04:46 PM
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
03-18-2009, 05:04 PM
I don't see a problem with the basic code. Can you post your workbook?

Xrull
03-18-2009, 06:28 PM
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
03-19-2009, 08:29 AM
I found the error. I rebuilt the file. I got my ranges right. :clap:
Thanks,
Xrull