VBA for If Statements

[27458]

I have a workbook with Sheet 1 contains names in A2:A100 and Months dates in B1:B12. Sheet 2 has sales figures with monthly dates in A:A, total sales in B:B and the sales name in C:C. The same person may have multiple sales in the same month on different lines.

I need a VBA procedure that will do an if statement that will total the sales for each name and return the results to sheet 1. For instance, if sheet 1 A2 has Randy and B1 has June, I need a VBA procedure to return the total sales for Randy in cell B2 and loop thru the names A2:A10 and return the values. And then do the same for the next month, in this case for Randy it would be in C2 for July. So, I'd need some type of offset.

Currently, I have if formulas to do this, but I'd like this to run in VBA to post only values and speed the process. Any help would be greatly appreciated.

 

[kieran]

27458,

This can be achieved without VBA or If statements too.

Construct a pivot table based on sheet 2, columns are the name, rows are the date, the data field is the sum of the sales.
It will look pretty messy on the first cut, but then click on any date field and group by months.
It should give you the same result. No code solution and pivot tables are generally faster than VBA solutions for this.

 

[acw]

Hi

IF you want to continue with the VBA, can you please respecifiy the ranges? Do you mean that the names on sheet1 are in the range A2:A10 and the dates are in the range B1:M1 with the sales in the intersection of date and name?

If not, where is the output going?


Tony

 

[27458]

Yes, sorry. The range is Names A2:A100 and dates B1:M1 and the totals value should intersect at date and name.

I've tried Pivot Tables, but have found graphs and formulas linked to Pivot Tables are not too favorable, such as sorting and ranking.

I was hoping to gain more knowledge of VBA.

Thx. Randy

 

[acw]

Randy

This is one way. It puts a formula to calculate the value, copies it, then turns the results into values. You will have to modify the data ranges to suit.

Tony

Sub bbb()
Sheets("sheet1").Range("b2").Formula = "=SUMPRODUCT(--(Sheet2!$A$1:$A$500=Sheet1!B$1),--(Sheet2!$C$1:$C$500=Sheet1!$A2),(Sheet2!$B$1:$B$500))"
Sheets("sheet1").Activate
Range("b2").Select
Selection.Copy Destination:=Range("b2:m100")
Range("b2:m100").Select
Selection.Copy
Selection.PasteSpecial (xlValues)

Range("a1").Select
Application.CutCopyMode = False

End Sub

 

[27458]

Excellent. This works perfect. Thanks for the help!