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

#### sssb2000

##### Well-known Member
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?

### 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
Re: Not sure if this can be done with a Macro but worth a Sh

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
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

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
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

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
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
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.

Replies
7
Views
128
Replies
5
Views
194
Replies
0
Views
119
Replies
10
Views
161
Replies
0
Views
85

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?

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