Code loop through two tables checking and altering

**excel_lover** · 11-12-2008, 03:34 PM

Hi I have a looping code problem I was wondering if you could have a quick look for me.

I have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.

Ive written some steps explaining what each table does here -

Step 1 Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2 Check corresponding column destination available capacity in table 2 (Example 500)
Step 3 Distribute as much as possible from source in table 2 (500)
Step 4 Reduce value in capacity line by value taken from source
Step 5 If some source remains move back to table 1 and find next nearest column Dest
Step 6 Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
etc
until Table 1 column B is empty

Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.

Thanks for taking a look.

**VBA Noob** · 11-12-2008, 04:20 PM

excel_lover,

Please read fourm rules below and then add the link to you're cross post

VBA Noob

**excel_lover** · 11-12-2008, 04:59 PM

Sorry VBA Noonb - I have posted this at mrexcel too.
http://www.mrexcel.com/forum/showthread.php?t=352898

**StephenR** · 11-12-2008, 05:03 PM

This works for your example.

Please Login or Register  to view this content.

**excel_lover** · 11-12-2008, 05:27 PM

Thanks Stephen thats brilliant. Ive tried expanding it though and get an error telling me -

"Unable to get the small property of the worksheet function class".

When I debug this it highlights the row containing

Please Login or Register  to view this content.

Ive posted my alterations below with the code alteration ive made. Where have i gone wrong?

Thanks

**StephenR** · 11-12-2008, 05:35 PM

I think just change this line so that it includes the row with the distribution amounts (you had B17):

Please Login or Register  to view this content.

The code now runs, although I haven't checked that it gives the right answer!

**excel_lover** · 11-12-2008, 05:39 PM

thanks a lot stephen thats some excellent coding

**StephenR** · 11-12-2008, 05:54 PM

Thank you, my pleasure.

**excel_lover** · 11-12-2008, 06:21 PM

Hi Stephen R,

Ive just tried to put it into a much much larger sheet of data and im getting the match problem again! - Ive attached an example of the scaled up version

Ive tried formats of the numbers, double checking the matrix ranges but still no go!

Is there any limitation to the number of columns?

Thanks

**StephenR** · 11-12-2008, 06:51 PM

Not sure, it falls over at the match line, although it works if you use the formulae in the worksheet. Not time now, perhaps somebody clever will have a bright idea.

Please Login or Register  to view this content.

**excel_lover** · 11-12-2008, 07:40 PM

Hi ive solved it, the numbers for the distances must be at least 1.
This has thrown up another problem now of double distribution! Ill post more if i cant fix it.
Thanks

**StephenR** · 11-13-2008, 02:25 AM

Actually, I made it work by changing one of the declaration statements, I'm mystified as to why it should make a difference, but use

Please Login or Register  to view this content.

at the start rather than

Please Login or Register  to view this content.

Btw, your second range is one column wider than your first - is that right?

EDIT: also, if you add the constraint that the distribution amounts must be positive it doesn't work.

**excel_lover** · 11-13-2008, 12:25 PM

Hi Stephen,

I got it to work every time now. It must see whole numbers only in the distance table. There can be no blanks in the source volume column of the second table aswell.

Ive made the

Please Login or Register  to view this content.

changes aswell.

The distribution is smooth and correct on a row by row basis but unfortunately the distribution isnt taking into consideration the destination capacity. If you run the code in the spreadsheet you can see that dest 4 gets 8372 more than once when really after the first time the capacity is used.

How can I change this?

Thanks

**StephenR** · 11-13-2008, 01:07 PM

Right, I think this works for your example. See attachment. A few of them exceed the destination capacity by 0.2 or something, but I'm guessing this is due to rounding.

Please Login or Register  to view this content.

**excel_lover** · 11-13-2008, 01:20 PM

Wow thats brilliant its worked first time.
Please can you tell me how to best learn how to do this myself.
I can write stuff like this

Please Login or Register  to view this content.

This is very logical and step by step but i just cant get my head around what youve done.

Any tips would be great

Thanks again

**StephenR** · 11-13-2008, 01:38 PM

It's just practice, it's all logical and we all start off knowing nothing and then picking up bits as you go. Many people here will have forgotten more than either of us will ever know! I've added some comments to the code below which may elucidate (or not perhaps).

Please Login or Register  to view this content.

**excel_lover** · 11-13-2008, 03:11 PM

You wont believe this but theres another issue stopping it from working!

Ive tested the latest version and altered the volume from all sources to 636.4k and the capacity at the destinations to 641.4k. There is 5k capacity but the volume wont distribute because it isn't counting the capacity or distributing to dest 22, missing out on 74.4k. When the distribution code is run as in the example it crashes because of the lack of capacity and highlights the code in debug as

Please Login or Register  to view this content.

Ive tried out various column and table placements to try and fix it, and tried a dummy row. When creating a dummy row the code works with the new tighter capacities but placements are incorrect when cross referenced with the distance table. The objective is to deliver first to dest with capacity then move to next nearest and so on.

If you run the distribute button on the spreadsheet attached you'll notice that the first source should all be distributed to dest 8 at 15 miles away but instead it is sent to dest 7 at 33.

Whats going wrong with the code to make it send to the wrong dest?

**StephenR** · 11-13-2008, 03:45 PM

There are two problems. One is that your layout keeps changing so that nMinCol needs to be incremented by one. The other is that when you have two (or more) equal values in the distance it always references the same one. I haven't worked out a solution yet, but I hope to be able to.

Better withdraw that "Solved"...

**StephenR** · 11-13-2008, 05:07 PM

Here is a workaround which is crude, but I think it works. If I think of something better I'll post it. Come back when you encounter the next problem...

Please Login or Register  to view this content.

**excel_lover** · 11-14-2008, 05:50 PM

thats it.. it all works now.. Thanks for helping me

Code loop through two tables checking and altering

LinkBack

Thread Tools

Rate This Thread

Display

Code loop through two tables checking and altering

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions