Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro or VBA to choose correct price from a chart

0

Hello, I'm new to Macro & VBA but really want to get my head around them, so thought I'd try this awesome new forum to see if someone could help me write my 1st proper 'program'!

On the attached workbook i need to have the unit price (Col. G) extracted from the relevant chart depending on the variables entered on each line - there are 4 pricing charts in accordance with which blind type or fabric range is selected and then within each chart there is the size to choose from.

Hopefully it makes sense but if any further explanation is required please ask.

UPDATE:

New attachment with macro added (slight modification of answer below) which works correctly except for the 3rd, 5th, 7th & 8th entries where the price for the next size range up is selected. Please help with what i need to change to bring the correct price through - this needs to be within the range which is smaller than or equal to the next size bracket (1450mm wide would be within the 1450mm width range whereas 1451mm wide is in the 1650mm width range)

Also, what do i need to change to have the pricing charts on a workbook stored on the local network.

Thanks in advance.

excelmacros-vba
MS Office Version: Windows 11 - Office 365
posted by: JonP (rep: 37) on Wed Jun 29, 2016 at 3:12 am
Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer and then click Yes to verify it.
don (rep: 1989) Jun 30, '16 at 11:04 am
Thanks Don. Sorry, not had a chance to try your answer yet and not doubting it will work. Quick question... i sent a simplified version of the full workbook and there are a number of additional options. I presume that i can simply add extra If and ElseIf arguements?
Also, is it possible for the worksheets containing the actual pricing charts to be on a seperate workbook saved on the local network?
Thanks again. Jon
JonP (rep: 37) Jun 30, '16 at 11:20 am
No worries, I'm still trying to figure out the best way to let people know about the whole "selecting an answer" thing since that's new for an Excel forum.

The answer to your questions are: it depends, and yes.

Yes, you can, usually, get the desired information from workbooks stored in other locations.

As for the other fields, if those other fields are used to determine from which worksheet you get the pricing data, then yes, that follows the same pattern as the current IF statements. The IF statements in the macro only figure out which worksheet to use and this method is easy to see visually but it can get complex if you add a lot of fields and a lot of options.
don (rep: 1989) Jun 30, '16 at 1:58 pm
Don
Thanks for your help so far, I'm really looking forward to your free training course on VBA but in the meantime, please help with what i change to make the macro look to a workbook saved on the network
JonP (rep: 37) Jul 1, '16 at 2:25 am
Also, should the 1st section not have 'Cells(Selection.Col, 1).Value' etc rather than 'Row'? 
JonP (rep: 37) Jul 1, '16 at 2:28 am
Don
Further to my 2 posts above, please ignore the last one as i now realise why and that you are correct here.
However, the code doesn't bring the result required and I've played around for quite a while but really don't know what to change.
The unit value extracted from the pricing chart needs to be from the cell equal to or smaller than the width & height it relates to (but larger than the next size down). Therefore, in my example file attached, the 1st entry should pull the unit price of £98.98 as being in the range of 1050mm wide x 1550mm high from worksheet 'Crank-Op Thames'.
Please help.
JonP (rep: 37) Jul 1, '16 at 3:17 am
So is width going from left-to-right and height up and down? B/c it looked like it was the other way around so that's what I put in the code.


And if you add any specifications, like access over the network or anything else please also edit your original answer so someone who comes to the page will know what's up without reading all the comments.  For now though let's get this basic macro working well and then add in the over the network access once everything is good.
don (rep: 1989) Jul 1, '16 at 12:16 pm
Okay, sorry to confuse. I have altered the code to the following and it now works great except for the 3rd line where the width is a lower figure than the smallest on the chart. (Code posted in next comment due to size)                                  
JonP (rep: 37) Jul 1, '16 at 1:00 pm
Sub price_calc()   blind_type = Cells(Selection.Row, 1).Value
fabric_range = Cells(Selection.Row, 2).Value
qty = Cells(Selection.Row, 3).Value
fab_width = Cells(Selection.Row, 4).Value
fab_height = Cells(Selection.Row, 6).Value   If blind_type = "Crank" Then     If fabric_range = "Thames" Then       ws_name = "Crank-Op Thames"     ElseIf fabric_range = "Medway" Then       ws_name = "Crank-Op Medway"     End If   ElseIf blind_type = "Chain" Then     If fabric_range = "Thames" Then       ws_name = "Chain-Op Thames"     ElseIf fabric_range = "Medway" Then       ws_name = "Chain-Op Medway"     End If   End If   row_location = Application.WorksheetFunction.Match(fab_height, Sheets(ws_name).Range("B1:B16")) + 1
col_location = Application.WorksheetFunction.Match(fab_width, Sheets(ws_name).Range("A2:Q2")) + 1   unit_price = Sheets(ws_name).Cells(row_location, col_location).Value   Selection.Value = unit_price   End Sub
JonP (rep: 37) Jul 1, '16 at 1:01 pm
Oh, i even put code tags but it changed it!
JonP (rep: 37) Jul 1, '16 at 1:01 pm
should i post my new version of your code as an answer?
JonP (rep: 37) Jul 1, '16 at 1:02 pm
Don't worry about the CODE tags thing I am still working on getting the bugs out of it. If that final version works for you then sure post it and select it as the answer. You will have to wait 24 hours before you can select it I think. I did that to discourage people from asking questions they already had the answer to.

If you could though, can you tell me "how" you tried to add CODE tags? Did you type them in by hand? Or, did you click the CODE button and paste the code in the middle of them? I just need to know this so I can try to fix the problem.
don (rep: 1989) Jul 2, '16 at 1:23 pm
Thanks Don.
I've been trying some more but the code I pasted above brings the correct price for some lines but not for all. Not sure if you can help. 
When pasting the code I clicked the code button and pasted between. 
JonP (rep: 37) Jul 2, '16 at 2:26 pm
Hey, can you edit your question and upload the excel file that you are using for testing that has the extra fields in it and I can test these macros against that? If not, i can just test it against the first file that you sent.
don (rep: 1989) Jul 2, '16 at 3:23 pm
I tried to download the file but it says the extension is wrong. I think somehow the file was not saved correctly before you uploaded it. I made a test post to ensure uploading xlsm files and downloading them works and didn't have an issue so I think it must have been something on your end.
don (rep: 1989) Jul 4, '16 at 2:49 pm
Have removed and resaved the workbook again and it worked when i tried downloading it - please try again.
JonP (rep: 37) Jul 5, '16 at 1:49 am
The file worked this time. Check out my latest update.
don (rep: 1989) Jul 5, '16 at 3:10 pm
Sorry Don but its now my turn to query whether the file saved correctly - i can't see any difference in the macro code and the extra column & row were blank. I tried inserting the extra column & row like you suggest below but the wrong price is pulled across so I'm not sure if i have the necessary alterations to the macro.
JonP (rep: 37) Jul 7, '16 at 2:47 am
I will upload another version of it showing the data in those columns. I had already "hidden" them by making the text the same color as the background.
don (rep: 1989) Jul 7, '16 at 11:23 am
You will see the extra row and column in the Chain-Op Thames worksheet. And the difference in the macro code is just the column/row that is referenced in the formulas at the bottom, A1:Q1 was the main change I think.
don (rep: 1989) Jul 7, '16 at 11:26 am
Apologies but my error, i thought that the hidden data was in column C & row 3! - It works great :)
Now what about extracting the prices from charts saved on the local network - the point of this is that there is only 1 set of pricing charts to keep up to date rather than every working sheet (a new one produced for every job)
JonP (rep: 37) Jul 7, '16 at 11:41 am
Ok, whew, haha. Moving on, calling it from the network is usually quite easy but is quite a different topic so go ahead and make a new question for it and I will give you some examples to try.
don (rep: 1989) Jul 7, '16 at 2:19 pm
Add to Discussion

Answers

0
Selected Answer

Try this:

Sub price_calc()

blind_type = Cells(Selection.Row, 1).Value
fabric_range = Cells(Selection.Row, 2).Value
qty = Cells(Selection.Row, 3).Value
fab_width = Cells(Selection.Row, 4).Value
fab_height = Cells(Selection.Row, 6).Value

If blind_type = "Crank" Then

  If fabric_range = "Thames" Then

    ws_name = "Crank-Op Thames"

  ElseIf fabric_range = "Medway" Then

    ws_name = "Crank-Op Medway"

  End If

ElseIf blind_type = "Chain" Then

  If fabric_range = "Thames" Then

    ws_name = "Chain-Op Medway"

  ElseIf fabric_range = "Medway" Then

    ws_name = "Chain-Op Medway"

  End If

End If

row_location = Application.WorksheetFunction.Match(fab_height, Sheets(ws_name).Range("A1:A16")) + 1
col_location = Application.WorksheetFunction.Match(fab_width, Sheets(ws_name).Range("A1:Q1")) + 1

unit_price = Sheets(ws_name).Cells(row_location, col_location).Value

Selection.Value = unit_price

End Sub

Most of the code is just to figure out which worksheet tab to use to get the info.

To use this, select the cell for which you want to get the unit price and then run the macro.

Update:

I updated the macro because it needed to be changed a bit in order for everything to work.

But, to get the macro to work for all instances, you need to adjust your data a little bit. You have to do this or the macro will have to get a bit more complex and difficult to maintain.

  1. Insert an empty row and empty column at the start of your pricing chart and place a 0 (zero) as the new "smallest" size.
  2. In the new empty space, type a formula that adds 1 to the current size.
  3. Reference the new cells in the macro, already done in the macro included above.
  4. Hide the new column/row of data by selecting it and making the text color white. You can also lock those cells if you want to keep them extra safe.

I did this for the "Chain-Op Thames" sheet and everything seems to be working well. Use that sheet as an example and do that for the other ones and it should work. File included below.

The other way to get around the issues is to reverse sort your pricing charts, which will look confusing, or beef up the macro a bit. Though, I think my method here is the easiest to maintain in the long-run.

(The data is already hidden in the Chain-Op Thames sheet, just select row 1 and column A and make the text black to see whats there.)

posted by: don (rep: 1989) on Wed Jun 29, 2016 at 1:09 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login