Refer to a cell in a table imported fromExcel (1 Viewer)

Bobp3114 · Sep 8, 2021

I have a table imported from Excel...(Screenshot below) It relates to freight charges and I am required to programme vba to allow the user to enter say 2.5 Kg and Zone 4 and have the answer 109.75. I have no idea so am looking for any ideas which will be much appreciated Thanks Bob

KG	Zone 1	Zone 2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7
0.5	44.80	54.82	55.52	58.49	60.68	72.33	107.84
1.0	55.82	69.35	69.99	71.21	74.27	89.55	134.55
1.5	64.02	82.17	82.83	83.67	87.11	105.65	157.95
2.0	72.22	94.99	95.67	96.13	99.95	121.75	181.35
2.5	82.12	108.55	108.67	109.75	114.92	142.01	209.61
3.0	88.08	114.77	115.61	120.45	126.73	155.07	228.51
3.5	94.04	120.99	122.55	131.15	138.54	168.13	247.41
4.0	100.00	127.21	129.49	141.85	150.35	181.19	266.31
4.5	105.96	133.43	136.43	152.55	162.16	194.25	285.21
5.0	111.92	139.65	143.37	163.25	173.97	207.31	304.11
5.5	114.58	144.39	148.04	168.93	178.83	213.81	314.65
6.0	117.24	149.13	152.71	174.61	183.69	220.31	325.19
6.5	119.90	153.87	157.38	180.29	188.55	226.81	335.73
7.0	122.56	158.61	162.05	185.97	193.41	233.31	346.27

Gasman · Sep 8, 2021

That is not normalised, so more work to do?
You should have a structure more along the lines of
KG
Zone
Rate

Make it like that then a simple DlookUP() would do what you need.

What happens if you have a parcel of 2.75 Kg?

Pat Hartman · Sep 8, 2021

This type of lookup is easiest when you have both start and end ranges in the table. That simplifies the criteria to:

Where KG Between StartKG and EndKG.

Without both start and end, you will need to use a sub query

MajP · Sep 8, 2021

Table1 Table1

ID	WeightStart	WeightEnd	Zone	Charge
1	0	0.5	Zone 1	44.80
2	0	0.5	Zone 2	54.8
3	0.5	1	Zone 1	55.82

As said if you make your table like this
then your query is where the user supplied weight is > weightStart AND <= weightEnd and the Zone = "User Supplied Zone"
(This assume the user might have something weighing some other weight (.4kg for ex.)
This can be done without any vba if you want or at maximum a one line dlookup.

You can create this table in Excel very easily. Use the Paste Special Transpose option to get

Zone 1	44.8	55.82	64.02	72.22	82.12	88.08	94.04	100	105.96	111.92	114.58	117.24	119.9	122.56
Zone 2	54.82	69.35	82.17	94.99	108.55	114.77	120.99	127.21	133.43	139.65	144.39	149.13	153.87	158.61
Zone 3	55.52	69.99	82.83	95.67	108.67	115.61	122.55	129.49	136.43	143.37	148.04	152.71	157.38	162.05
Zone 4	58.49	71.21	83.67	96.13	109.75	120.45	131.15	141.85	152.55	163.25	168.93	174.61	180.29	185.97
Zone 5	60.68	74.27	87.11	99.95	114.92	126.73	138.54	150.35	162.16	173.97	178.83	183.69	188.55	193.41
Zone 6	72.33	89.55	105.65	121.75	142.01	155.07	168.13	181.19	194.25	207.31	213.81	220.31	226.81	233.31
Zone 7	107.84	134.55	157.95	181.35	209.61	228.51	247.41	266.31	285.21	304.11	314.65	325.19	335.73	346.27

Then cut and past the columns into this and add in the weight endpoints.

WeightStart	WeightEnd	Zone	Charge
0	0.5	Zone 1	44.8
0	0.5	Zone 2	54.82
0	0.5	Zone 3	55.52
0	0.5	Zone 4	58.49
0	0.5	Zone 5	60.68
0	0.5	Zone 6	72.33
0	0.5	Zone 7	107.84
0.5	1	Zone 1	55.82
0.5	1	Zone 2	69.35
0.5	1	Zone 3	69.99
0.5	1	Zone 4	71.21
0.5	1	Zone 5	74.27
0.5	1	Zone 6	89.55
0.5	1	Zone 7	134.55
1	1.5	Zone 1	64.02
1	1.5	Zone 2	82.17
1	1.5	Zone 3	82.83
1	1.5	Zone 4	83.67
1	1.5	Zone 5	87.11
1	1.5	Zone 6	105.65
1	1.5	Zone 7	157.95

Then import the new table into access.

MarkK · Sep 8, 2021

You don't need both start and end weights in the row.

Code:

SELECT TOP 1 Charge FROM tCharge
WHERE [prmWeight] <= Kg AND Zone = [prmZone]
ORDER BY Kg DESC

MajP · Sep 8, 2021

You don't need both start and end weights in the row.

As stated it is not required, but highly recommended if you are doing more than just looking up a single value. If instead of a single value you have a table of values then you will have to use a subquery as previously stated. Subqueries are inefficient and somewhat difficult to write for many users.

Bobp3114 · Sep 8, 2021

Gasman said:
That is not normalised, so more work to do?
You should have a structure more along the lines of
KG
Zone
Rate

Make it like that then a simple DlookUP() would do what you need.

What happens if you have a parcel of 2.75 Kg?

Thanks mate, I will digest this...2.75 is to round up

Bobp3114 · Sep 8, 2021

MajP said:
Table1 Table1

ID WeightStart WeightEnd Zone Charge
1
0
0.5
Zone 1 44.80
2
0
0.5
Zone 2 54.8
3
0.5
1
Zone 1 55.82

As said if you make your table like this
then your query is where the user supplied weight is > weightStart AND <= weightEnd and the Zone = "User Supplied Zone"
(This assume the user might have something weighing some other weight (.4kg for ex.)
This can be done without any vba if you want or at maximum a one line dlookup.

You can create this table in Excel very easily. Use the Paste Special Transpose option to get

Zone 1
44.8
55.82
64.02
72.22
82.12
88.08
94.04
100
105.96
111.92
114.58
117.24
119.9
122.56
Zone 2
54.82
69.35
82.17
94.99
108.55
114.77
120.99
127.21
133.43
139.65
144.39
149.13
153.87
158.61
Zone 3
55.52
69.99
82.83
95.67
108.67
115.61
122.55
129.49
136.43
143.37
148.04
152.71
157.38
162.05
Zone 4
58.49
71.21
83.67
96.13
109.75
120.45
131.15
141.85
152.55
163.25
168.93
174.61
180.29
185.97
Zone 5
60.68
74.27
87.11
99.95
114.92
126.73
138.54
150.35
162.16
173.97
178.83
183.69
188.55
193.41
Zone 6
72.33
89.55
105.65
121.75
142.01
155.07
168.13
181.19
194.25
207.31
213.81
220.31
226.81
233.31
Zone 7
107.84
134.55
157.95
181.35
209.61
228.51
247.41
266.31
285.21
304.11
314.65
325.19
335.73
346.27

Then cut and past the columns into this and add in the weight endpoints.

WeightStart WeightEnd Zone Charge
0
0.5
Zone 1
44.8
0
0.5
Zone 2
54.82
0
0.5
Zone 3
55.52
0
0.5
Zone 4
58.49
0
0.5
Zone 5
60.68
0
0.5
Zone 6
72.33
0
0.5
Zone 7
107.84
0.5
1
Zone 1
55.82
0.5
1
Zone 2
69.35
0.5
1
Zone 3
69.99
0.5
1
Zone 4
71.21
0.5
1
Zone 5
74.27
0.5
1
Zone 6
89.55
0.5
1
Zone 7
134.55
1
1.5
Zone 1
64.02
1
1.5
Zone 2
82.17
1
1.5
Zone 3
82.83
1
1.5
Zone 4
83.67
1
1.5
Zone 5
87.11
1
1.5
Zone 6
105.65
1
1.5
Zone 7
157.95

Then import the new table into access.

Thanks Mate, I will wrap my old mind around this (80yrs). seem the solution

Bobp3114 · Sep 8, 2021

MarkK said:
You don't need both start and end weights in the row.

Code:

SELECT TOP 1 Charge FROM tCharge WHERE [prmWeight] <= Kg AND Zone = [prmZone] ORDER BY Kg DESC

Thanks for all replies.. see how I go Have to fit this into other requirements

Bobp3114 · Sep 8, 2021

MajP said:
Table1 Table1

ID WeightStart WeightEnd Zone Charge
1
0
0.5
Zone 1 44.80
2
0
0.5
Zone 2 54.8
3
0.5
1
Zone 1 55.82

As said if you make your table like this
then your query is where the user supplied weight is > weightStart AND <= weightEnd and the Zone = "User Supplied Zone"
(This assume the user might have something weighing some other weight (.4kg for ex.)
This can be done without any vba if you want or at maximum a one line dlookup.

You can create this table in Excel very easily. Use the Paste Special Transpose option to get

Zone 1
44.8
55.82
64.02
72.22
82.12
88.08
94.04
100
105.96
111.92
114.58
117.24
119.9
122.56
Zone 2
54.82
69.35
82.17
94.99
108.55
114.77
120.99
127.21
133.43
139.65
144.39
149.13
153.87
158.61
Zone 3
55.52
69.99
82.83
95.67
108.67
115.61
122.55
129.49
136.43
143.37
148.04
152.71
157.38
162.05
Zone 4
58.49
71.21
83.67
96.13
109.75
120.45
131.15
141.85
152.55
163.25
168.93
174.61
180.29
185.97
Zone 5
60.68
74.27
87.11
99.95
114.92
126.73
138.54
150.35
162.16
173.97
178.83
183.69
188.55
193.41
Zone 6
72.33
89.55
105.65
121.75
142.01
155.07
168.13
181.19
194.25
207.31
213.81
220.31
226.81
233.31
Zone 7
107.84
134.55
157.95
181.35
209.61
228.51
247.41
266.31
285.21
304.11
314.65
325.19
335.73
346.27

Then cut and past the columns into this and add in the weight endpoints.

WeightStart WeightEnd Zone Charge
0
0.5
Zone 1
44.8
0
0.5
Zone 2
54.82
0
0.5
Zone 3
55.52
0
0.5
Zone 4
58.49
0
0.5
Zone 5
60.68
0
0.5
Zone 6
72.33
0
0.5
Zone 7
107.84
0.5
1
Zone 1
55.82
0.5
1
Zone 2
69.35
0.5
1
Zone 3
69.99
0.5
1
Zone 4
71.21
0.5
1
Zone 5
74.27
0.5
1
Zone 6
89.55
0.5
1
Zone 7
134.55
1
1.5
Zone 1
64.02
1
1.5
Zone 2
82.17
1
1.5
Zone 3
82.83
1
1.5
Zone 4
83.67
1
1.5
Zone 5
87.11
1
1.5
Zone 6
105.65
1
1.5
Zone 7
157.95

Then import the new table into access.

Hi MajP, Managed to transpose the spreadsheet OK (learn something every day). In my instance where the user is quoting a possable sale and wishes to enter a product and get a freight cost for that product, do I need the weightstart and weightend? It seems that the requirement is one need at a time. The final equation is something like: Total Import sharges = Caculated Company Rate + Calculated Emergency Surcharge + Calculated Fuel Surcharge + Calculated Customs Duty. This freight rate that we are working on is used in calculating the Fuel Surcharge for example.
Thanks Bob

MajP · Sep 9, 2021

do I need the weightstart and weightend?

I assume the reason this is in a database is because you are doing database like things. I assume instead of doing one calculation you are doing a tables worth of calculations 10s, 100s, 1000s, millions of records. As I pointed out in all likelihood you will want to quickly do this without a subquery. So I recommend the upper and lower bound. I have to assume again you are not limited to weights of .5, 1, 1.5...7, but you can have weights like 3.75 kg. I already stated the lower bound is not required, but likely to make things much easier.

Sure you do not have to fix anything you could have just wrote vba code against your original table, but then why even bother putting this in a database. Again I am assuming you are doing database things and not doing one simple calculation.

It seems that the requirement is one need at a time.

You do not build an application to solve one problem, and then completely rewrite it as you want to add more functionality. You write it to be flexible to handle all likely functions.

arnelgp · Sep 9, 2021

your kg/zone is small, so you can keep its current structure.

Bobp3114 · Sep 9, 2021

Excellent...above my level of VBA but seems to be giving a result for a KG twice that entered eg I enter 4.5 in the Kg box and 2 in the Zone box i get a result of 133.3 which is the number for 9KG?

arnelgp · Sep 9, 2021

while the form is open, you open also the table (behind).
first of all 9 is the ID (autonumber), not the KG and you don't
have 9KG in the table.
it is correctly retrieving the "Value".
if you enter a Kg that is not in the table, say, 1.8kg, zone 1,
it will retrieve the value for 2kg, zone1 = 72.22.
since 2kg is "nearer" to 1.8kg than 1.5kg.

2 - 1.8 = 0.2
1.8 - 1.5 = 0.3

MajP · Sep 9, 2021

I am sorry, but @arnelgp just proved what not to do.
1. Solution requires a lot of VBA
2. The solution is overly complicated
3. The solution is not flexible. It cannot support a table of values easily. You cannot easily update the table or the combobox

The fact that it can be done does not make it a good approach. The reason I, @Gasman, and @Pat Hartman all recommended the same approach is because this is not the first time we have done this or worked with others on the forum to do the exact same thing. It is simple to implement, efficient, and flexible. Not sure why there is so much Heart burn to properly structure the table. It took me six minutes to make by cutting and pasting.

Then my entire code becomes simply

Code:

Private Sub cboZone_AfterUpdate()
  UpdateRate
End Sub

Private Sub txtKG_AfterUpdate()
UpdateRate
End Sub

Public Sub UpdateRate()
  If Me.txtKG > 0 And Me.txtKG <= 7 And Not IsNull(Me.cboZone) Then
    Me.txtResult = DLookup("Rate", "tblZoneRates", Me.txtKG & " >= WeightRangeStart AND " & Me.txtKG & " <= WeightRangeEnd AND ZONE = '" & Me.cboZone & "'")
  End If
End Sub

Also you can enter any value between 0 and 7.
But most importantly this table can be used easily in other Queries. And again I assume that is the whole purpose of putting it into a database, to do database things. Finally if you add edit zones, change weights no code modifications are necessary, You just type in the data table.

MajP · Sep 9, 2021

Not sure if you are planning to round or not and what the rounding rule is. But in my example if you are rounding then you would round the input based on your rounding rule before looking for the rate.

MarkK · Sep 9, 2021

Using a single rate per row...

RogerCooper · Sep 9, 2021

I have faced the same problem, which I solved by computing an average cost/lb for each zone and a per carton charge. The results were in a few % and I rounded it up to the nearest whole dollar. It was good enough for our purposes.

Pat Hartman · Sep 9, 2021

@RogerCooper - Whatever makes you happy but I can tell you with absolute certainty - it is best to do it right the first time and I have the scars to prove it

@Bobp3114 Except for arnel, the experts have been united on how this task should be handled. It is actually a common task and frequently the data starts out exactly as yours did. @MajP even converted the data for you. It took him 6 minutes because he knew what he was doing. You could have done it manually in an hour or two by simply typing the data in a properly formatted table. Or you might have struggled for a day to figure out how to do it with a query.

@MarkK Your solution works in the narrow context of your example. The query as it is returns ALL rows where the weight is >= the argument. You could fix this by adding Top 1. I'm not sure whether it will work if there is also a date constraint. That is the next question the OP will ask. Prices change and you have to put the prices in with a start date and an expiration date or null so that multiple prices for the same criteria can exist at the same time with the one to choose being dictated by the date range. I'm pretty sure why this need for a date range is what causes the need for a subselect if you don't have both start and end for the weight and the effective date range.

Bobp3114 · Sep 10, 2021

arnelgp said:
while the form is open, you open also the table (behind).
first of all 9 is the ID (autonumber), not the KG and you don't
have 9KG in the table.
it is correctly retrieving the "Value".
if you enter a Kg that is not in the table, say, 1.8kg, zone 1,
it will retrieve the value for 2kg, zone1 = 72.22.
since 2kg is "nearer" to 1.8kg than 1.5kg.

2 - 1.8 = 0.2
1.8 - 1.5 = 0.3

I have managed toimport my spreadsheet in to replace your existing table (my table ranges from 0 to 300KG) and your form is working

Refer to a cell in a table imported fromExcel (1 Viewer)

Member

Enthusiastic Amateur

Super Moderator

You've got your good things, and you've got mine.

bit cruncher

You've got your good things, and you've got mine.

Member

Member

Member

Member

You've got your good things, and you've got mine.

..forever waiting... waiting for jellybean!

Attachments

Member

..forever waiting... waiting for jellybean!

You've got your good things, and you've got mine.

Attachments

You've got your good things, and you've got mine.

bit cruncher

Attachments

Registered User.

Super Moderator

Member

Users who are viewing this thread