Struggling with a macro

  • Thread starter Thread starter toolpusher
  • Start date Start date
T

toolpusher

Hi

Want to have a macro that will hold a value in its memory and subtrac
a different value from the value in the memory then write the result
to a cell in a column as I proceed down the column. Then I need t
change the value held in the meory as so on. Any ideas

regards

Joh
 
You need to be more specific. For instance,

Where will the initial value held "in memory" come from?

What should the macro use as its "different value?

Should the macro operate on the same column each time it's run? If not,
how should it know which column?

How far down the column should the macro proceed before the value in
memory is changed?

Should this happen automatically or only when you click a button/type a
keyboard shortcut, etc.
 
Pusher,

Sub WriteDifference()
Dim Value1, Value2
Dim i As Long

Range("A1").Select ' set starting cell
Value1 = 15 ' set initial value
Value2 = 10
For i = 1 To 10
Selection = Value2 - Value1 ' write difference to cell
Value1 = Value1 - 1 ' change value 1
Value2 = Value2 + 2 ' change value 2
Selection.Offset(1, 0).Select ' move down
Next i ' do it again

End Sub

The For - Next loop could be replace by a Do - Loop, or other means of
looping and controlling when to stop. Using Selection to write cells isn't
the preferred way, but is straightforward for a starter.
 
Maybe I don't understand what you are after, but why not enter your 1st
value in cell A1, enter your second value in B1. In C1 place formula
=A1-B1

then you can change the value in either A1 or B1 and your result
changes in C1

You can then copy this formula down column C as far as needed and fill
cols A & B with whatever values you like.

Is this what you are trying to do?
 
Hi

Sorry guys for being so vauge will try again.

I have a list of 9000 products in column A
In column B is their price

Some of these are the options of a product

So in row 1 we have 100g red hammer @ £10 it has two options below
In row 2 we have 200g red hammer @ £11
In row 3 we have 300g red hammer @ £12 -- and so on some products do
not have options some have up to 40. And all the prices are different
and do not increase by %

I have inserted a column next to the price column. *I need to record
the price difference of an option to its product.*
Soo column C is for holding the price difference

So I need a macro that when its activated it will first remember the
value in B1
Then when I move to C2 it will take the B2 from B1 and insert the
price difference.
I then move to C3 and it take B3 from B1. and so on until I need to
sart all over again with a new product price

I have a feeling I have made my self no clearer here but I have tried

Kind regards

John
 
It's less vague now, but it's hard to see how XL should determine
whether a item is an option or a new item.

Is the last word in column A always the same for the different options,
and different when the item changes?

Is a 200g blue hammer a different item than a 100g red hammer, or is it
an option?

Would a 200g blue framing hammer be a new option, or a different item,
than a 200g blue claw hammer?
 
JE

Thanks for putting up with me here. Theres also loads of other column
as well with different info in them. So theres also a column of I
numbers. This hammer might be called 1060/100G its two options will b
called 1060/200G & 1060/300g and so on. I know I cant do this in on
automated go I will need to do it manully.

Its just a nightmare when you get a drill bit with 40 options from 2m
(£1.00) to 40mm (£8.00) I see the price of them all in column B but it
the price difference of the option compared to the 2mm drill bit I nee
so that the 40 mm would read £7.00

regards

Joh
 
Toolpusher
I know all this makes perfectly good sense to you but you have to
realize that you in the minority. From your several attempts at explaining
this, I gather that you have reams of data consisting of a number of groups
of data. Each group is what you want to work with when you say you want to
hold one number constant and subtract numbers from it for each of what you
call options within that group.
Then when you enter the next group, you do the same thing but the base
number changes.
If you wish, send me direct a file with some examples of several groups
of raw data. Within this file but in another sheet, have the same data but
with the final product included. Include an explanation of how your numbers
were derived for the final product. My email address is
(e-mail address removed). Remove "nop" from this address. HTH Otto
 
toolpusher said:
JE

Thanks for putting up with me here. Theres also loads of other columns
as well with different info in them. So theres also a column of ID
numbers. This hammer might be called 1060/100G its two options will be
called 1060/200G & 1060/300g and so on. I know I cant do this in one
automated go I will need to do it manully.
Click to expand...

if you are satisfied to make some part manualy, try this:
use additional column, and place there =p$r, where p$r is cell address of
reference/base model of class, than copy that formula into each row
belonging to variants.
now, each variant has info of its base article prise.
all you need, is additional column with difference price, where formula is
simple
difference between two cells on the same row.

the only manual thing is that you need to maintain first additional column.
 
Back
Top