Posts: 48
Threads: 18
Joined: Sep 2005
Hi,
In my work i often have excel sheet with product prices i need to change (add certain % to the price).
How can i make a macro that :
- Search for cells that have numbers in format x.x ; x.xx; x.xxx (price)
- display dialog box with data (x&z)to fill:
if value 0-x1 add z1%
if value x1-x2 add z2%
if value x2-x3 add z3%
if value x3-x4 add z4%
- Compare search results with these conditions and change values accordingly.
If any one could help on this one i`d appreciate it, it`d help my work a lot.
Posts: 12,073
Threads: 140
Joined: Dec 2002
are the prices in single column?
please design the dialog and post here. It would make more clear.
Posts: 48
Threads: 18
Joined: Sep 2005
Yes the prices are usually in 1 column, but some time in two. but if this is a problem i can copy single price column to empty sheet and after macro runs over it copy it back.
About the dialog. I havent done it before so it might take a looong time to provide it. Maybe i dont need the dialog, i can change the constant variables in the code manually.
The thing is that the % added to the price always depends on the value of the initial price. Like if price is 0.52 usd i add like 20% to the price but for example if the price is higher like 1000 usd i add only 2% and so on. So each time i run the macro for different sheet i need to set % i add for given price range (or use deflaut). So i need to control two things the price range and % to add. For ex. i set 4 price range and 4 different % to add accordingly:
price value from 0 to 2.5 add 20%
price value from 2.51 to 10 add 18%
price value from 10.1 to 20 add 15%
price value from 20.1 and above add 11%
This is why i thought dialog would be good here, but i can also change it in the code each time i execute macro. Hope that`s clear enough.
Posts: 12,073
Threads: 140
Joined: Dec 2002
Macro Macro1278
;/exe 1
;The /exe 1 tells to run in separate process. It is needed on Vista/7, if UAC is on.
;Warning: When Excel sheet changed in this way, you cannot Undo. You should backup your workbook file or sheet.
;define prices
type PRICERANGE ^maxPrice ^percentAdd
ARRAY(PRICERANGE) ap; PRICERANGE& p
&p=ap[]; p.maxPrice=2.5; p.percentAdd=20 ;;0 - 2.5
&p=ap[]; p.maxPrice=10; p.percentAdd=18 ;;2.51 - 10
&p=ap[]; p.maxPrice=20; p.percentAdd=15 ;;10.01 - 20
&p=ap[]; p.maxPrice=0; p.percentAdd=11 ;;20.01 - oo
;You can change the above values.
;You can add more price ranges, or delete some. Prices must be in ascending order. The last price is not used and can be 0.
;connect to current Excel sheet and get all cells
ExcelSheet es.Init
ARRAY(str) a
es.GetCells(a)
;enumerate cells
int c r i
for r 0 a.len(2) ;;for each row
,for c 0 a.len(1) ;;for each column
,,;here you can insert code to skip some columns. Example: sel(c) case [1,2] case else continue ;;skips columns other than B and C
,,str& s=a[c r] ;;cell
,,;out s
,,if(findrx(s "^[\d,]+(\.\d{1,3})?$")<0) continue ;;is it currency format?
,,double d=val(s 2) ;;need to convert to a numeric type to do calculations
,,;out d
,,for(i 0 ap.len-1) if(d<=ap[i].maxPrice) break ;;find range
,,d+d*ap[i].percentAdd/100 ;;add %
,,d=Round(d 2) ;;max 2 digits after .
,,es.SetCell(d c+1 r+1) ;;replace cell
Posts: 1,000
Threads: 253
Joined: Feb 2008
You can also use excel to do this...
=IF(A1<=2.5,A1*1.2,IF(A1<=10,A1*1.18,IF(A1<=20,A1*1.15,IF(A1>20,A1*1.11))))
Setting up cells with your test values is a good idea in case you ever want to change them on the fly. It's always good to avoid hard-coding your values:
C1=2.5, C2=10, C3=20
D1=1.20, D2=1.18, D3=1.15, D4=1.11
=IF(A1<=$C1,A1*$D1,IF(A1<=$C2,A3*$D2,IF(A1<=$C3,A3*$D3,IF(A1>$C3,A3*$D4))))
The "$" sign prevents the value from being updated when you copy and paste or expand the formula over multiple cells.
Basically locks that cell into position, where A1 will change relative to the location of the formula.
Then Copy the new data...paste special only values over the old data....Otherwise you'll be pasting the formula which will result in unwanted results.
Posts: 1,000
Threads: 253
Joined: Feb 2008
By the way...beautiful writing Gintaras!
I noticed your defining a lot of ARRAY types lately.
Could you write up a tutorial with some step-by-steps using general examples at different levels. These definitely have come in handy with the last few questions I have asked of you and I would definitely like to have a better understanding.
I guess I really don't understand how the "&" works.
ARRAY(PRICERANGE) ap; PRICERANGE& p
&p=ap[]; p.maxPrice=2.5; p.percentAdd=20 ;;0 - 2.5
What does the & sign do? Why does it not work without it?
Thank you for QuickMacros!!
Outstanding work!
jimmy vig
Posts: 48
Threads: 18
Joined: Sep 2005
Thanks G! Works like a charm. 
Posts: 12,073
Threads: 140
Joined: Dec 2002
Quote:What does the & sign do? Why does it not work without it?
Look in QM Help -> "Pointer, reference, array" topic.
p is reference. Initially it is reference to nothing. &p=ap[] sets it to be reference to the new array element that is added by ap[]. Now p.something is the same as ap[x].something. Here I used reference to make easier to add/remove price ranges because don't need to use array index (ap[0], ap[1], etc).
Maybe will help this example with files:
variable is file.
reference is shortcut.
&reference=variable is like you set file path in shortcut's Properties dialog.
|
