Speed Up VBA for Bulk Goal Seek

[leeandoona]

I'm running a bulk Goal Seek/Set using the following code over 10,000 rows. It works great but it takes about a day to run the macro! Is there any advice on how I can make it work Faster?

Sub LOWEST_BULK_GOAL_SET()
Dim iRow As Long

For iRow = 2 To 10001
Cells(iRow, "N").GoalSeek Goal:=0.05, ChangingCell:=Cells(iRow, "F")
Next iRow
End Sub

 

[leeandoona]

If your markup is expressed as a percentage of CostPriceExVAT, then I think you simply need:

TSV = [CostPriceExVAT(1+MarkUp%) + ShippingExVAT + ChannelFees$ExVAT] / [1-ChannelFees%ExVAT - VAT/(1+VAT)]

See if the calculations below do what you need? You'll need the rounding parameter in B6 set to 2, which may produce the occasional £0.01 rounding discrepancies in the margin.

File for download: Box

Book1
	A	B	C	D
1	Channel	Amazon	(eBay or Amazon)
2	VAT	20%	(0% of 20%)
3	Cost Price	£50.00	incl VAT
4	Shipping Cost	£8.00	incl VAT
5	Markup	10.0%	on Cost price ex VAT
6	Round to	2	decimal places
7
8		Fee	VAT	incl VAT
9	Channel Fees	15.0%	3.0%	18.0%
10	PayPal	0.0%		0.0%
11		£0.00		£0.00
12	Third Party	3.0%	0.6%	3.6%
13	Packaging	£0.15	£0.03	£0.18
14	Total Channel Fees	18.0%	3.6%	21.6%
15		£0.15	£0.03	£0.18
16
17	Cost Price	£41.67	£8.33	£50.00
18	Shipping Cost	£6.67	£1.33	£8.00
19
20	VAT claim		£9.69
21		plus:	3.0%	of TSV
22
23	TSV:			£80.60
24	less:
25	Cost Price ex VAT claimable			£41.67
26	Shipping cost ex VAT claimable			£6.67
27	Channel fees ex VAT claimable			£14.66
28	VAT payable on TSV			£13.43
29				£76.43
30	Margin			£4.17
31	cf: Required margin			£4.17
Sheet1

Cell Formulas
Range	Formula
C9, C12:C13	C9	=VAT*B9
B10	B10	=IF(Channel="eBay",2%,0)
B11	B11	=IF(Channel="eBay",0.2,0)
D9:D11, D12:D13	D9	=SUM(B9:C9)
B14:D14	B14	=B9+B10+B12
B15:D15	B15	=B13+B11
B17:B18	B17	=ROUND(D17/(1+VAT),N)
C17:C18	C17	=D17-B17
D17:D18	D17	=B3
C20	C20	=C13+C17+C18
C21	C21	=C9
D23	D23	=ROUND((SUM(B15:B18)+B5*B17)/(1-(B14+VAT/(1+VAT))),N)
D25:D26	D25	=B17
D27	D27	=ROUND(B14*D23,N)+B15
D28	D28	=ROUND(D23*VAT/(1+VAT),N)
D29	D29	=SUM(D25:D28)
D30	D30	=D23-D29
D31	D31	=B5*B17

Cells with Data Validation
Cell	Allow	Criteria
B1	List	eBay,Amazon
B2	List	0%,20%

Thanks so much for this very detailed reply. I've downloaded the file and I'll take a close look and compare it to the existing process to see how it works and report back.

 

[leeandoona]

Thanks so much for this very detailed reply. I've downloaded the file and I'll take a close look and compare it to the existing process to see how it works and report back.

Okay, I've checked this is working against the existing maths and it seems to be coming out the same! Which is great. Now the challenge is to have this work as the current cumbersome process does but miles faster! Currently I would normally use my large and cumbersome spreadsheet to set the variables (Choosing the Shipping method, the sales channel and the vat status) and then run the macro on it to create a TSV for each increment of 1p of cost based on the cost price and I would then have to call up a VLookup against the cost price file to carry out a price update. I would of course need to do that 8 times for 8 seperate Vlookups against the cost price file, one for each variable instance of cost. For example

Vatable eBay Large Letter 0.01p through £100.00
NonVatable eBay Large Letter 0.01p through £100.00
Vatable eBay Packet 0.01p through £100.00
NonVatable eBay Packet 0.01p through £100.00

Vatable Amazon Large Letter 0.01p through £100.00
Amazon NonVatable Large Letter 0.01p through £100.00
Amazon Vatable Packet 0.01p through £100.00
Amazon NonVatable Packet 0.01p through £100.00

Hence why I was originally trying to speed up my macro as each time I run all this, it takes almost a whole day to get the resulting pricing files! Is this the basis for setting that up in VBA that you mentioned earlier above?

Cheers

 

[StephenCrump]

Great, I'm glad the formula appears to be working.

I haven't mentioned/considered VBA previously. If you don't need to be using GoalSeek, why bother setting up 8 lookup tables with 10,000 price points? Surely you can calculate eight TSVs directly for each item price:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1				Channel	eBay	eBay	eBay	eBay	Amazon	Amazon	Amazon	Amazon
2				VAT	20%	0%	20%	0%	20%	0%	20%	0%
3				Postage	LL	LL	Pckt	Pckt	LL	LL	Pckt	Pckt
4
5	Item	CostPrice		TSVs -->
6	A	£0.40			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
7	B	£3.70			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
8	C	£14.00			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
Sheet1

e.g. Formula for TSV calculation in G6 will point to Channel = eBay in G$1, VAT=20% in G$2, and Postage = Pckt in G$3.

 

[leeandoona]

Great, I'm glad the formula appears to be working.

I haven't mentioned/considered VBA previously. If you don't need to be using GoalSeek, why bother setting up 8 lookup tables with 10,000 price points? Surely you can calculate eight TSVs directly for each item price:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1				Channel	eBay	eBay	eBay	eBay	Amazon	Amazon	Amazon	Amazon
2				VAT	20%	0%	20%	0%	20%	0%	20%	0%
3				Postage	LL	LL	Pckt	Pckt	LL	LL	Pckt	Pckt
4
5	Item	CostPrice		TSVs -->
6	A	£0.40			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
7	B	£3.70			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
8	C	£14.00			£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx	£xx.xx
Sheet1

e.g. Formula for TSV calculation in G6 will point to Channel = eBay in G$1, VAT=20% in G$2, and Postage = Pckt in G$3.

Ah I see what you mean. Sorry I think we are at cross threads. I thought you meant that first I'd need to re-arrange the formulas to then use the VBA to create the new pricing files.

DOH! Yes, stupid me. I'm still thinking about doing the other way and of course I wouldn't need to would i! I will do as you suggest and create a file that shows the price in columns. The resulting spreadsheet the formula repeated downwards over each row to show a price for each penny increment and then I suppose it would culminate with me just needing to Vlookp each column on this to return the latest price. Its a different way of doing it but I think it could work much smoother actually! Why I didn't take this approach before I'm not sure!!! Just stupid I guess!

Thanks so very much indeed for your help with this. It's been a right pain trying to resolve this so that it could speed up my already busy day. Can't thank you enough!

 

[StephenCrump]

We may be still be slightly at cross threads?

It sounds like you're still proposing to have a giant lookup table, with 10,000 rows (i.e. prices from £0.01 to £100.00) and 8 columns for the various Channel/VAT/postage combinations. Then if you have 20,000 items, say, you'll have 20,000 x 8 = 160,000 VLOOKUPs to retrieve all the TSV combinations.

I'm saying bypass the lookup table, and calculate TSVs directly based on the actual item price. So in Post #13, G6 is calculated directly based on the item cost price of £0.40. it is not a VLOOKUP pointing to the £0.40 row of a giant lookup table.

How many different items do you have for sale?

 

[leeandoona]

We may be still be slightly at cross threads?

It sounds like you're still proposing to have a giant lookup table, with 10,000 rows (i.e. prices from £0.01 to £100.00) and 8 columns for the various Channel/VAT/postage combinations. Then if you have 20,000 items, say, you'll have 20,000 x 8 = 160,000 VLOOKUPs to retrieve all the TSV combinations.

I'm saying bypass the lookup table, and calculate TSVs directly based on the actual item price. So in Post #13, G6 is calculated directly based on the item cost price of £0.40. it is not a VLOOKUP pointing to the £0.40 row of a giant lookup table.

How many different items do you have for sale?

Sorry late reply, been away for funeral.
Ah yes I see what you mean. Unfortunately I don't think that will work practically as we have approximately 30,000 items all priced erratically at cost with variants of vatable and non vatable so either way, I need to use some form of automation to look at the cost, it's vat status and work out the retail taking all the fees etc into account. Hence the original method. Your method is much neater than my original and does the same thing per cost....and if I had a few dozen costs to plug in then it would be suffice to keep it like that. However, the volume I'm dealing with dictates the need for automation. I've tried your version using my vlookup process and it is much smoother, but still takes an age to return the results. Its the volume.