​ Hello,</SPAN>



I would very grateful if you can please help with the problem I have run into. We sell on Amazon and I am trying to automate our costing sheet so based on margins our selling price can be decided. However, I am struggling as the Amazon fee is a % of the price and hence has a further impact on your margin.

I have uploaded a test excel sheet, with two sheets, here is the link: https://www.dropbox.com/s/88gfbjzigervvh3/Test.xlsmI have also attached the sheet below if that would be easier.


I have attempted to use a VBA code which is shown below:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim r As Range
 
Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub

Now, I have a few problems. The code sort of works, however it does not tackle these issues:

1. It only works for one row at a time, we have thousands of products on our other sheet and I want a code which will cover the entire spreadsheet.

2. I have to paste this code in every sheet, is there a way to put it on the work book

3. We want our min price to be written on the min price column, and same with the max price on the max price column, based on the % specified in other column. How can we run this simultaneously?

4. The cost EX VAT is the addition of B2:E2, however, I have just used F2 which is Cost of EX VAT in the VBA shown, therefore if cost changes, automatically price should change. But this does not work, on the other hand if I specify B2, C2, D2… in the code individually then it works. Why is this?

I do not have amazing knowledge on VBA as I am learning, any help will be greatly appreciated. If you feel like there is an easier way to complete this task, please do say.

Thanks for taking time to help or even read this.

Kind Regards,
Champaben

Attached Files

Test.xlsm (19.2 KB, 15 views)

Champaben, my job is heating up just now and I probably won't spend more than a few sentences at a time with you, not as much help as you'll need. Also I don't know anything about GoalSeek—never used it.

But I can answer one of your questions:The Worksheet_Change subroutine will have to be on every sheet. But you can paste some simple code on every sheet that calls a central subroutine, and have that central subroutine in a common place. That way, whenever you have to change the code, you change it in the central location and all the Worksheet_Change modules, by calling that subroutine, will stay up to date without you having to change each one of them.

I also suspect that only some of that code should be in VBA. Just at a glance, reading your description, it sounds to me as though some of what you're trying to do in VBA would be done more efficiently in worksheet formulae.

Vague generalities like that probably aren't much help to you, I know. But I hope someone else will come back and pay more specific attention. Meanwhile I hope my idea about the Worksheet_Change modules is some help. Feel free to ask more questions to force me to clarify.

Hello BobBridges,

Thanks for your response, it is greatly appreciated.

In regards to the basic excel functions, I have tried various things but have had no luck. Please tell me what features I can use, according to me the reason it does not work is simply because we have a fee which is a percentage of our selling price and then drives the cost up, hence the margin drops further.

I understand what you are saying in regards to getting it on every worksheet, but I am awful at excel and trying to learn, can you please help me with the code I should enter, and if you could kindly look at my other queries when you get a chance as they are more important.

Thanks.

Hi

As to the formula in question it's rather a math-thing. If you can calculate the value for G2 on a piece of paper you can simple introduce it to excel.

What you got now:
M2 = (G2/1.2 - F2)/G2
From there you want to know the value of G2, let's have it done (in case you missed the class for whatever reason ):
M2 * G2 = G2/1.2 - F2
M2 * G2 / G2 = G2/(1.2 * G2) - F2/G2
---> simplified: M2 = 1/1.2 - F2/G2
M2 + F2/G2 = 1/1.2
F2/G2 = 1/1.2 - M2
1/G2 = (1/1.2 - M2)/F2
G2 = 1/((1/1.2 - M2)/F2)

It will give you a accurate result, whereas GoalSeek only provides a result by iterations. Calculation by iteration is stopped at a certain point, to achieve a result in reasonable time...
Therefore I spare what the macro must look like to achieve dynamically a less acurate result by GoalSeek

Hello,

Thanks for your reply, but that does not work.

It simply cannot work as E2 is dependent on G2 as mentioned previously.

Thanks anyway.

You're right by one thing: Yes, I did miss the reference in E2 to G2

But using my piece of paper a second time it says:
G2 = 1/((1/1.2-0.15-M2)/(B2+C2+D2))

Now the question remains if I got it wrong at some point. I don't think so, but it always makes me suspicious if somebody else says "it can't be done" assuming he/she tried on his/her own, and therefore knows what he/she is talking about. So please just check if the formula is doing allright, otherwise you probably will have to do with GoalSeek.

I haven't checked whatsup's formula, but I agree with him that some formula would work and be faster than using GoalSeek. But for the sake of explaining VBA, I'll continue with your original problem, because that's worth knowing even if the formula is better this time.

I'll do this in steps. The first step is to create a module where you can put the "common code", the VBA statements that every worksheet will call. Here's how you do it in Excel 2010 (and if you're using some other version let me know):

1) Start in the VBA editor.

2) Right click anywhere inside the red box in the below screen shot.

3) Select "Insert", then "Module". An empty module will be created in the Modules section. (You see in the screen shot that I already have one created.)

4) Optional: I named mine "mGoalSeek"; you can leave yours named "Module1" or whatever VBA names it by default, or you can change it to your own name by typing over the Name in the Properties window. Let me know if you have trouble with this and I'll show you another screen shot.

The new module is ready for you to type in, creating any new subroutines or functions.

Next I'll describe how to write a subroutine that your individual worksheets can call....in a separate post.

Attached Images

x1.png (43.3 KB, 17 views)

Hello,

Thanks for your response.

That does not bring up the right answer on all cells, hence I cannot use that. I have found another method.

Thanks anyway.

Thanks for your input and time, I have figured the problem out.

Step two: Calling a common module from each of the worksheets.

You already have a start because you have a Worksheet_Change subroutine written in one of your worksheets:I'll show you how to change that around so this subroutine calls another one. Here's a subroutine that I named DoGoalSeek with all those statements copied into it almost unchanged:This code I write into the new module you just created. Now it can be called by any other subroutine in your workbook, like this:Here's what happens: When you change any cell in a worksheet, Excel runs the Worksheet_Change subroutine for that worksheet (if any). The only thing that subroutine does, as I've modified it above, is call DoGoalSeek and pass it the Target object (which is a pointer to the cell that was changed).

DoGoalSeek takes the Target object passed to it and does everything that used to be in your Worksheet_Change subroutine. When it's done, it passes control back to Worksheet_Change, which returns control back to you.

Now you can paste the same three-line subroutine into Sheet2, and into any other price sheet you have in your workbook, and each one will call the same logic every time a cell is changed. Then when you have to change the way DoGoalSeek works—and we will change it, in several ways—you can change it in just one place instead of in every worksheet.

Next post: What we have to do to DoGoalSeek to make it work for any row, not just row 2.

Just for the record, the answer is yes. You can use the workbook_change event in the ThisWorkbook module.
ImageUploadedByTapatalk1403431309.336709.jpg

Really? A Worksheet_Change routine in the ThisWorkbook module will work for every worksheet? That's cool, I never knew. I don't think I've ever had to have the same Worksheet_Change logic for every worksheet in a workbook, so (I realize now) I was just assuming.

Wait—what happens if I have a Worksheet_Change procedure in both modules, ThisWorkbook and a worksheet? Does Excel get upset, or does it just let the worksheet override the workbook?

It's a hierarchy Bob. In this case it's two levels.
Starting from the lowest level:
Worksheet
Workbook or Application
It's actually called Workbook_Sheetchange in fact.
The event cascades from bottom (sheet) to top (book), it's easy enough to check the order with msgbox. A couple of events, for example mouse events have a Cancel parameter which can be set to True to stop the cascade at that level but the Sheet Change event is not one of them. The Sheet Change events are however raised sequentially, so the worksheet level event handling routine (if present) is run to completion before the workbook (application) level event is raised. Thus avoiding the potential for conflict.
The reason I say workbook (application) is because they are one in the same thing. It's complicated, but when you add a Workbook_SheetChange routine in the ThisWorkbook module you are actually processing the Application.SheetChange routine.