Macro Help

[Peanut]

I need some help writing a macro.

Here is some data similar to mine. Customer 1 made $250 worth of purchases
on 4/4, Customer 2 made $300 of purchases on 4/4, etc. (This information,
essentially, is a lookup from a import tab that will be refreshed daily)


4/4/2006

Customer 1 $250.00
Customer 2 $300.00
Customer 3 $1,000.00


I also have another worksheet that records the transactions over time. The
format is like the following:

Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

Can a macro lookup the date in B1 and paste the values (transposed of
course) in the second worksheet next to the corresponding date?

Thanks in advance.

 

[Mike H.]

Change the data in the first sheet to have the date in a column:
Customer 1 4/4/06 $250.00
Customer 2 4/4/06 $300.00
Customer 3 4/4/06 $1,000.00

Then do a sumproduct in the second sheet:
the $250 in the intersection of "Cust 1" (Colum B) and 4/4/06 (Row 2) would
be:

=SUMPRODUCT((Sheet1!A1:A2500=Sheet2!B1)*(Sheet1!B1:B2500=Sheet2!A2)*(Sheet1!C1:C2500))

Note you can just say A:A, B:B and C:C if using Excel 2007. Otherwise you
have to have a finite limit to the number of rows.


Cust 1 Cust 2 Cust 3
4/4/2006 $250.00 $300.00 $1,000.00
4/5/2006 $300.00 $100.00 $-
4/6/2006 $75.00 $50.00 $300.00

 

[Peanut]

This won't work. The data in the first worksheet is not static. Which is
the reason why I need the macro to paste the values into a separate
spreadsheet.

 

[Mike H.]

So if I understand your response correctly, you're saying you get a new set
of sales periodically (maybe daily) and then you wish to summarize the data
on sheet1 on sheet2. But sheet1 only contains yesterday's sales In that
case:

Sub Addemup
dim DataArray(5000,3) as variant
Dim TheDate as date
Dim X as double
Dim fnd as double
Dim found as integer
Dim Y as double

sheets("sheet1").select
let TheDate = ??? (cell containing the date in sheet1
let x=1

do while true
if cells(x,1).value=empty then exit do
let found =0
for y=1 to fnd
if dataarray(y,1)=cells(x,1).value then
dataarray(Y,2)=dataarray(Y,2)+cells(x,2) 'add amounts for same
customer!
found=1
exit for
end if
next
if found=0 then
fnd=fnd+1
dataarray(Fnd,1)=cells(x,1).value 'populate customer # into array
DataArray(Fnd,2)=cells(x,2).value 'populate Sales Amt #1 into Array
end if
x=x+1
Loop

sheets("sheet2").select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
x=activecell.row+1
cells(x,1).value=TheDate
let y=2
do while true
if cells(1,y).value=empty then exit do
for z=2 to fnd
if cells(1,z).value=dataarray(Y,1)
cells(X,Z).value=dataarray(y,2)
dataarray(Y,3)="FOUND"
exit for
end if
next
y=Y+1
Loop
z=z-1
'now make sure all customers are in sheet2
for Y=1 to fnd
if dataarray(Y,3)<>"FOUND" then
z=z+1
cells(1,z).value=dataarray(Y,1) 'put customer # on row 1
cells(x,z).value=dataarary(Y,2)
end if
Next


end sub

 

[Peanut]

Yes, you are understanding me correctly. There is one piece of your code
that doesn't work. Its in the second loop in sheet2. I am getting a syntax
error on the following statement:

if cells(1,z).value=dataarray(Y,1)

 

[Mike H.]

Add the word Then after it. I typed all that code in hte response box and
did not try to compile it. There is a dataarray mis-spelled toward to bottom
also. Good luck!