Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro referencing identical text
Hi
I am new to Macros and think they are great. I work in food manufacturing and do a lot of work in costing materials usage etc Everyday I run a file from my ERP system from work and generate it as an excel file. I then do loads of calculations based on aggregating the financial costs of items so for instance I may want to look at 'ingredients' by aggregating jam, sugar, glucose etc. The trouble is that these items are not necessarily on the same row each time I run the report from my ERP. Is there anyway I can create a macro that will locate and calculate the materials by their names rather than a cell reference? Regards Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro referencing identical text
A simple way, assuming that your data is stored in single rows, would be to
scan all rows and using some reference cell that contains the name of the product, you then run a set of code. Something like..... Sub Test() Dim xRow As Long, xLastRow As Long With Sheets("Sheet1") xLastRow = .Cells(Rows.Count, 1).End(xlUp).Row For xRow = 1 To xLastRow ' put some generic code here that applies to every row Select Case .Cells(xRow, 1) Case Is = "Jam" ' some code here to process jam Case Is = "Sugar" ' some code her to process sugar Case Is = "Glucose", "Syrup" ' some code here to process glucose or syrup End Select ' put some generic code here that applies to every row Next End With End Sub -- Cheers Nigel wrote in message oups.com... Hi I am new to Macros and think they are great. I work in food manufacturing and do a lot of work in costing materials usage etc Everyday I run a file from my ERP system from work and generate it as an excel file. I then do loads of calculations based on aggregating the financial costs of items so for instance I may want to look at 'ingredients' by aggregating jam, sugar, glucose etc. The trouble is that these items are not necessarily on the same row each time I run the report from my ERP. Is there anyway I can create a macro that will locate and calculate the materials by their names rather than a cell reference? Regards Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro referencing identical text
Hi Nigel
Thanks for your kind help. It seems a bit over my head but will give it a go Cheers Brian Nigel wrote: A simple way, assuming that your data is stored in single rows, would be to scan all rows and using some reference cell that contains the name of the product, you then run a set of code. Something like..... Sub Test() Dim xRow As Long, xLastRow As Long With Sheets("Sheet1") xLastRow = .Cells(Rows.Count, 1).End(xlUp).Row For xRow = 1 To xLastRow ' put some generic code here that applies to every row Select Case .Cells(xRow, 1) Case Is = "Jam" ' some code here to process jam Case Is = "Sugar" ' some code her to process sugar Case Is = "Glucose", "Syrup" ' some code here to process glucose or syrup End Select ' put some generic code here that applies to every row Next End With End Sub -- Cheers Nigel wrote in message oups.com... Hi I am new to Macros and think they are great. I work in food manufacturing and do a lot of work in costing materials usage etc Everyday I run a file from my ERP system from work and generate it as an excel file. I then do loads of calculations based on aggregating the financial costs of items so for instance I may want to look at 'ingredients' by aggregating jam, sugar, glucose etc. The trouble is that these items are not necessarily on the same row each time I run the report from my ERP. Is there anyway I can create a macro that will locate and calculate the materials by their names rather than a cell reference? Regards Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to add different characters to identical data in a column ? | Excel Worksheet Functions | |||
Macro Which Highlights Rows That Contain Identical First Cells | Excel Programming | |||
Counting number of identical values (or text) in a range | Excel Programming | |||
Macro works, identical Function doesn't....why? | Excel Programming | |||
Inexplicable difference in row hiding speed - identical code, identical machines! | Excel Programming |