To challanging for me, Need help!!!

[bedsy]

Hi All,

reasonably new to VBA and need some help with my project.

I have a spread sheet that has customer abbreviation in Column "F" and a range of numbers "L:AA"

what I need is VBA code to find all occurrences for a each customer in column "F" and add each column individually and place total of each column in the corresponding cell on row 3.

"F" 5 (Row 3)
Customer 1 AB 1
AB 4
Customer 2 AC 6
AD 5

I have the below code with finds the customer "AB" and calculates the corresponding columns and places the total in row 3 but only works for the 1st column, not the complete row


Private Sub CommandButton1_Click()
Dim myval As Integer
Dim MyRange As Range
Dim MyRange2 As Range
Dim cell As Range

Set MyRange = Worksheets("Forecast").Range("F:F")
Set MyRange2 = Worksheets("Forecast").Range("L4:AA300")
myval = Application.WorksheetFunction.SumIf(MyRange, "AB", MyRange2)
Range("L3").Value = myval
End Sub

Can someone smarter than me please assist.

Thanks all.

 

[tygrrboi]

Short Answer.. you cant do a sumif with multiple sum columns. You can do a SumProduct like this =SumProduct(F4:F300 * (L4:AA300 = "AB"))


so for your VBA... Try this.

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]
[COLOR=#333333]Dim MyRange As Range, [/COLOR][COLOR=#333333]MyRange2 As Range
[/COLOR][COLOR=#ff8c00]'Some declarations removed because they were not necessary... [/COLOR]

[COLOR=#333333]Set MyRange = Worksheets("Forecast").Range("[/COLOR][COLOR=#ff0000]F4:F300[/COLOR][COLOR=#333333]") [/COLOR][COLOR=#ff0000]'changed to an actual range... not the entire column...[/COLOR][COLOR=#333333][/COLOR]
[COLOR=#333333]Set MyRange2 = Worksheets("Forecast").Range("L4:AA300")[/COLOR]
[COLOR=#0000ff]
Range("L3").Value = Evaluate("SUMPRODUCT(" & MyRange2.Address & "* (" & MyRange.Address & "=""AB""))")
'Took away the integer declaration, myval, since you weren't really doing anything with it... [/COLOR]

[COLOR=#333333]End Sub

[/COLOR]

 

[bedsy]

Thanks tygrrboi..