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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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" <(E-Mail Removed)> skrev i melding
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
>
>

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

"Eirik Sævareid" <(E-Mail Removed)> wrote in message
news:O5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i melding
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >
>
>