Macro Help!! 10 mins of your time need PLEASE!
 

Hi All

I have a spreadsheet which i need a macro creating for (or any other
method that will make it work)

Basically i need it to sum data but the data isnt in a consistent
table.

Rather than try to copy the spreadsheet to here, please email me at
and i will send you the spreadsheet.

I have added coments to the spreadsheet to show what i need the macro
to do.

HOPE SOMEONE CAN HELP, I am new to excel and this intrests me.

Regards

Paul


--
pauldaddyadams
------------------------------------------------------------------------
pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
View this thread: http://www.excelforum.com/showthread...hreadid=393228

Macro Help!! 10 mins of your time need PLEASE!
 

Why don't you try to post your explanation here itself.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39322

Macro Help!! 10 mins of your time need PLEASE!
 

Here goes then! : ) Sorry if the data is a bit complicated!!!

Looking at the data below the important data is the material and plt
mvt column in the example this is 2000102 and the -79. I need a macro
or formula which identifies a product, this is always 7 digits long and
then it returns the sum of the products sold, in the qty column. So the
first one is the sum of -16,-1,-4,-2,-15,-5,-5,-11,-3,-3,-12-2 which
returns the number -79. I did this manully but I need an automatic way
of achieving this.

So for instance in the second part the product is 2000130 and i need in
the next column, in between the product code and description, a
formula/macro to add up -5 and -15.

The problem im facing is getting excel to identify what to sum : (

Apoligies again as the date looks complicated and squashed!! : )

HOPE SOMEONE CAN HELP ME!

Paul






PRODUCT Total Quantity

2000102 -79 BKG 6.0 CL G 3210x2250 LS 25 PKGL
NE 9062 Pilkington Basingstoke
21 201 GI for cost
ce 4908476752 4 29.07.2005 29.07.2005 16:53:56 -16 PL 9062
21 201 GI for cost
ce 4908471029 4 28.07.2005 28.07.2005 17:58:18 -1 PL 9062
21 201 GI for cost
ce 4908469189 5 27.07.2005 28.07.2005 11:52:19 -4 PL 9062
21 201 GI for cost
ce 4908468863 4 26.07.2005 28.07.2005 10:53:32 -2 PL 9062
21 201 GI for cost
ce 4908456241 4 21.07.2005 26.07.2005 10:11:41 -15 PL 9062
21 201 GI for cost
ce 4908458612 4 22.07.2005 26.07.2005 16:57:44 -5 PL 9062
21 201 GI for cost
ce 4908428234 7 20.07.2005 20.07.2005 15:55:26 -5 PL 9062
21 201 GI for cost
ce 4908395787 5 14.07.2005 14.07.2005 12:47:51 -11 PL 9062
21 201 GI for cost
ce 4908397313 5 14.07.2005 14.07.2005 17:44:56 -3 PL 9062
21 201 GI for cost
ce 4908376227 4 11.07.2005 11.07.2005 13:22:14 -3 PL 9062
21 201 GI for cost
ce 4908363766 4 05.07.2005 08.07.2005 17:22:00 -12 PL 9062
21 201 GI for cost
ce 4908339205 6 01.07.2005 05.07.2005 10:12:42 -2 PL 9062

2000130 FAB 6.0 AB G 3300x2440 LS 16 9062 Pilkington
Basingstoke
21 201 GI for cost
ce 4908428234 13 20.07.2005 20.07.2005 15:55:26 -5 PL 9062
21 201 GI for cost
ce 4908363766 8 05.07.2005 08.07.2005 17:22:00 -15 PL 9062


--
pauldaddyadams
------------------------------------------------------------------------
pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
View this thread: http://www.excelforum.com/showthread...hreadid=393228

Macro Help!! 10 mins of your time need PLEASE!
 

The data is as you have shown, right. That is, is there a blank lin
between each group. Also the cells to be summed are in which column.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39322

Macro Help!! 10 mins of your time need PLEASE!
 

send it he

Macro Help!! 10 mins of your time need PLEASE!
 

Hi,

Yes there is a blank line between each product. The data i need it to
total is the colum in between the time and the PL columns. They all
start with a '' - ''

e.g. It is the middle column i need it to toal

16:53:56 -16 PL
17:58:18 -1 PL
11:52:19 -4 PL
10:53:32 -2 PL
10:11:41 -15 PL
16:57:44 -5 PL
15:55:26 -5 PL
12:47:51 -11 PL
17:44:56 -3 PL
13:22:14 -3 PL
17:22:00 -12 PL
10:12:42 -2 PL


--
pauldaddyadams
------------------------------------------------------------------------
pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
View this thread: http://www.excelforum.com/showthread...hreadid=393228

Macro Help!! 10 mins of your time need PLEASE!
 

Try this UDF. Put the code in a standard module.

Function MySum(rng As Range, col As String)

Set sRng = Cells(rng.Row + 1, col)
Set lRng = Cells(rng.End(xlDown).Row, col)

MySum = Evaluate("=Sum(" & sRng.Address & ":" & lRng.Address
")")

End Function

And then in the cell where you have shown -79, put the formula:
=mysum(A3,"L")

Where A3 is the cell which contains 2000102 and "L" is the column
which has to be summed. Repeat for the next.

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=39322

Macro Help!! 10 mins of your time need PLEASE!
 

i have solution... but i have also a lunchtime :-)
so be patient ;-)

Macro Help!! 10 mins of your time need PLEASE!
 

Thanks for all your help on this!

I have done what Mangesh suggested and this has worked, dont know how
but im very happy it does.

That is one part out of the way, Would there be a a way that a macro
could be written to identify each uniquie 7 digit product code and
apply the =mysum(A9,"L") formula?

This was i would have to manualy enter the formula as i have alot of
data to do this for.

Paul


--
pauldaddyadams
------------------------------------------------------------------------
pauldaddyadams's Profile: http://www.excelforum.com/member.php...o&userid=22499
View this thread: http://www.excelforum.com/showthread...hreadid=393228

Macro Help!! 10 mins of your time need PLEASE!
 

Yes you can,

Search for columns A:A for 7-digit codes, and when you find one, put the
formula in the adjacent cell.

Use the following code for the purpose:

Private Sub CommandButton1_Click()

Set rng = Worksheets("Sheet1").Range("A1:A1000") ' change 1000 to a largest
row

For Each cl In rng
If Len(cl) = 7 And IsNumeric(cl) Then
cl.Offset(0, 1) = "=mysum(" & cl.Address & ",""L"")"
End If
Next

End Sub

Assumption: Your 7-digit codes are in column A and total number of rows you
are using is 1000

Mangesh



"pauldaddyadams"
<pauldaddyadams.1tafuk_1123239956.8961@excelforu m-nospam.com wrote in
message news:pauldaddyadams.1tafuk_1123239956.8961@excelfo rum-nospam.com...

Thanks for all your help on this!

I have done what Mangesh suggested and this has worked, dont know how
but im very happy it does.

That is one part out of the way, Would there be a a way that a macro
could be written to identify each uniquie 7 digit product code and
apply the =mysum(A9,"L") formula?

This was i would have to manualy enter the formula as i have alot of
data to do this for.

Paul


--
pauldaddyadams
------------------------------------------------------------------------
pauldaddyadams's Profile:
http://www.excelforum.com/member.php...o&userid=22499
View this thread: http://www.excelforum.com/showthread...hreadid=393228