Automatization routine

[Eirik Sævareid]

Dear all,

I am doing a study of product costs versus revenues for a set of products at
a company. It is a developed a "price model" which returns a product cost
based on input in three cells. I am going to carry out a study for about
200 products.

Then I wonder if it is possible to develop a macro or something that can do
this in a simple way (for me to avoid punching all the values). The input
data is stored in an excel sheet (one row for each product). The product
codes (which are input data in the price model) for a given product may be
stored in cells A2, B2, and C2 (for example) in a sheet called "data".
These data should then be entered into three cells in sheet "pricemodel"
(for instance B2, C2, D2), and a result comes in cell E2 in sheet
"pricemodel". This result should then be transferred to cell D2 in sheet
"data". Then the routine should continue with the next product (cell A3,
B3, C3 in sheet "data") until this has been done for all 200 products.

Is there any way to automatize this task (macro or another way) ?

Please revert if my explanation is insufficient.

All help will be greatly appreciated.

Best regards,
Eirik Saevareid

 

[NickHK]

Eirik,
The easiset way would be to create a custom worksheet function, so you just
enter, in the required cell, say E2:
=PriceModel(A2,B2,C2)
and the answer is returned. Copy down, to calculate all 200.

In the VBA editor, add a Module. Add this code:

Public Function PriceModel (arg1 as Double, arg2 as Double, arg3 as Double)
as Double
PriceModel =arg1*arg2/arg3
End function

OK, the return value's and arguments' data type and inner code depends on
how you price model works, but that is up to you.

NickHK

 

[Eirik Sævareid]

Hello,

Thank you for your answer. I am sorry that I have made a bad/insufficient
explanation of the problem.

The Price Model is a quite complex spreadsheet. The calculation is based on
a lot of underlying data/formulas, and I think it is not possible to replace
this by a function. What I am looking for is a routine that picks up the
three input data (stored in a row in sheet "data") for each product, enter
them into the input cells in sheet "pricemodel" (B2, C2, D2 for instance)
and then pick ut the result from cell E2 in sheet "pricemodel" and return it
to cell D2 in sheet "data". When this is done, the calculation is carried
out for the first product.

When this is done for the first product, cells B2, C2, and D2 in sheet
"pricemodel" should be emptied, and the values for the second product (cells
A3, B3, C3 in sheet "data") should be entered into cells B2, C2, D2 in sheet
"pricemodel", and the result for the second product should be returned to
cell D3 in sheet "data". This should then continue until it is done for all
about 200 products. I suppose some kind of macro may be needed.

The values have to be put into those three input cells (B2, C2, D2) in the
sheet "pricemodel", it is (from my point of view) not possible to copy these
cells downward in the sheet "pricemodel", because of a lot of underlying
formulas which is included in the price model.

Was this an acceptable clarification, and do anyone have a solution ?

Best regards,
Eirik

 

[NickHK]

Eirik,
OK, then assuming you have a named range (rngData) of the first column of
your input data, something like this untested code:

Private Sub CommandButton2_Click()
Dim cell As Range
Dim ModelSheet As Worksheet

Set ModelSheet = Worksheets("PriceModel")

For Each cell In Range("rngData")
'Set the input values
ModelSheet.Range("B22").Value = cell.Resize(1, 3).Value
'Ensure calculation, if not automatic
ModelSheet.Calculate
'Read the result value back, into the next column
cell.Offset(0, 4).Value = ModelSheet.Range("E2").Value
Next

End Sub

NickHK