Not sure if this can be done with a Macro but worth a Shot!

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
I have a pool of numbers in lets say A1
I'm trying to distribute this pool, one number at a time, to the appropriate cells.

The way that the distribution takes place is:

Code would search through column T. If the cell (which may be a merged cell) contains "F/S1" , then the code would memorize it. There is a capacity for each cell in T. that capacity is given in Column X (in the row infront of the T column Cell)

The goal is to distribute the A1 pool to smallest of the F/S1 capacity values. So lets say we have 100 in A1. The code would search through Col. T, and finds 250 "F/S1"s
The code would then look at their appropriate col. X value and selects the lowest 100 values....and adds 1 (100 times....from A1) to each capacity value in col. X.


is this something a macro can do? (y)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
L

Legacy 12630

Guest
Re: Not sure if this can be done with a Macro but worth a Sh

I can't follow any of that.

A reconstruction of your question might be required.

(Pool of numbers? Capacity?)
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541
Re: Not sure if this can be done with a Macro but worth a Sh

Short answer: Yes.

Long answer: You might need to break your problem into smaller pieces. It's difficult to answer such a complex question. Let's start with the first bit. You say there's a "pool" of numbers in A1. All the numbers are in A1? Are they separated with commas, spaces, dashes?

Try posting a piece of your worksheet to the board, so others can get an idea of what you're trying to do.
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
Re: Not sure if this can be done with a Macro but worth a Sh

I'm sorry.
i often get so involved in the problem at hand, that i forget the fact that others haven't dealt with it for as long as i have and basically have no idea what the situation looks like.... my appologies again!


A1 contains ONE number (i used the term pool incorrectly....) that # is what needs to be distributed.

i have simplified the problem...i need that number (# in A1) to be distributed (one at a time) to numbers in column Y. So the macro would search col. Y, (some cells may not have numbers) , and once all are found, an internal sort is done within the macro and then the macro would adds 1 to each number starting at the smallest number....until A1 becomes 0.


Does that make more sense?
 
L

Legacy 12630

Guest

ADVERTISEMENT

Re: Not sure if this can be done with a Macro but worth a Sh

sssb2000 said:
Does that make more sense?

No.
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
Re: Not sure if this can be done with a Macro but worth a Sh

ok

i have a number in A1
i have a bunch of numbers in Col. Y

i want to search Col. Y
find the lowest number, Add 1 to it
subtract 1 from A1
find the next lowest number in Y, add 1 to it
subtract 1 from A1

Do this until A1=0

:) I hope this makes sense hehe
 
L

Legacy 12630

Guest

ADVERTISEMENT

Re: Not sure if this can be done with a Macro but worth a Sh

Code:
Dim rng As Range, x&, cell As Range
If Not IsNumeric([A1]) Or [A1] = 0 Or Len([A1]) = 0 Then Exit Sub
Set rng = Range([Y1], [Y65536].End(xlUp))
If [A1] > Application.WorksheetFunction.Count(rng) Then
    For Each cell In rng
        If IsNumeric(cell) And Len(cell) <> 0 Then
            [A1] = [A1] - 1
            cell = cell + 1
        End If
    Next
Else
    x = Application.WorksheetFunction.Small([Y:Y], [A1])
    For Each cell In rng
        If IsNumeric(cell) And Len(cell) <> 0 And cell <= x Then
            [A1] = [A1] - 1
            cell = cell + 1
            If [A1] = 0 Then Exit For
        End If
    Next
End If
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Re: Not sure if this can be done with a Macro but worth a Sh

Man Ponsonby, that is definitely slick.
I understood what the OP was asking for from the beginning but didn't know quite how to go about determining the smallest values in the range.
My hat's off to you.
What you wrote is very cool.

Dan
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
Re: Not sure if this can be done with a Macro but worth a Sh

Indeed!!!
Ponsonby,
what you wrote does EXACTLY what i needed! i did not have to modify a single thing!
i'm amazed that it works with merged cells just as well! Thank you very much.
my hat's off to you as well!


Kev.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,440
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top