Replies

Hi Steve

Thanks for your advice so far which I am trying to understand and process, my knowledge of Excel is limited (extremely) and a lot of what you have advised is quite frankly beyond me. I inherited this workbook with just the first 2 pages and I posted a requested to the effect that could it be made interactive to calculate the rates based on a destination country and then to calculate the rates based on weight and zone, Hans as mentioned kindly supplied page 3 for me which I have been trying to breakdown and understand with limited success.

A2 on the existing calculation page takes its values from the zones page although I cannot see how this happens as no formula shows when I am inside cell A2 (I have tried the help feature for show formulas but it does not show anything), when you type in a country name and press tab the zone in B2 automatically enters its self using the formula =VLOOKUP(A2,Zones!A4:B215,2,FALSE), when the weight is input into C2 and the weight is calculated and shown in I2 (obviously there are other hidden columns D,E,F,G 2 that behave in a similar way to I2) using the formula =IF(D2<=70.5,E2,IF(D2<=99,F2,IF(D2<=299,G2,IF(D2<=499,H2,"Unknown")))).

Putting in the Combo as suggested certainly allows me to select the Country from the Zones page but it does not allow me to tab out of it the update B2 and then enter the weight in C2 to do the calculation.

I hope this makes sense and is not asking too much of you.

TIA

Stephen

Hi Steve

I used your answer on the full blown version of my workbook so the calculation (as far as I understand it) should have worked, I will perservere though

Cheers

Steve

Hi Steve

I used your answer on the full blown version of my workbook so the calculation (as far as I understand it) should have worked, I will perservere though

Cheers

Steve

Hi Legare

Thanks for the response, I cannot get internet access at the weekends hence the late response. Yes I made sure that it was linked to A2 as Steve had suggested I should but it was not referring to the formula if that makes sense.

Cheers

Steve

Hi Legare

Thanks for the response, I cannot get internet access at the weekends hence the late response. Yes I made sure that it was linked to A2 as Steve had suggested I should but it was not referring to the formula if that makes sense.

Cheers

Steve

Hi Steve

Thanks for this, I had in fact already put a combo there when I was trying to do this myself and it does indeed act as a combo, however it does not then perform the calculation based on that Country. If you were to type in Anguilla in A2 as the workbook currently stands it will change the zone to 4 and when the weight is entered it will cross refer to the rates and surcharges sheet to select and calculate the appropriate rate. Hopefully that makes some sense.

Thanks

Stephen

Hi Steve

Thanks for this, I had in fact already put a combo there when I was trying to do this myself and it does indeed act as a combo, however it does not then perform the calculation based on that Country. If you were to type in Anguilla in A2 as the workbook currently stands it will change the zone to 4 and when the weight is entered it will cross refer to the rates and surcharges sheet to select and calculate the appropriate rate. Hopefully that makes some sense.

Thanks

Stephen

Thanks Hans and Steve for the prompt response

Stephen

Hi Hans,

Thank you, that must have been the quickest answer I have seen.

The Country and the zone work fine, however when I put in 55.5 kilos I would expect to see

Hi Hans,

Thank you, that must have been the quickest answer I have seen.

The Country and the zone work fine, however when I put in 55.5 kilos I would expect to see

Thanks for the response Steve

I have double clicked the button and put it what you suggested so that I end up with this

Private Sub CommandButton1_Click()
Sub PrintMultCopies()
Dim intCopies As Integer
intCopies = Application.InputBox(“Enter number of copies to print”, “Print”, 1, , , , 1)
If intCopies < 1 Then Exit Sub
ActiveSheet.PrintOut Copies:=intCopies
End Sub

When I press the button it highlights the line Private Sub CommandButton1_Click()

If I take this out I can click the buton without it putting up and error but it does not work, any other ideas?

Cheers

Stephen

Thanks Hans spot on as usual

Stephen

Thanks Hans spot on as usual

Stephen

Thank you Hans but in a way that works to perfectly in so far as if a user was to say input a weight of 14.5 kilos to say Belguim this formula correctly returns the result of

Thank you Hans but in a way that works to perfectly in so far as if a user was to say input a weight of 14.5 kilos to say Belguim this formula correctly returns the result of