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

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

Macro referencing identical text
 

Hi
Yes it can be done, using the Find Method to locate the text and from
there using offset to locate the numbers you want. It might also be
possible using AutoFilter and use of the SumTotal worksheet function
without using macros.
Impossible to give any more detail or decide on the best approach
without seeing how your Excel sheet(s?) is laid out and exactly what
calculations you need to do.
regards
Paul

wrote:
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

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