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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,698
Messages
5,855,230
Members
431,713
Latest member
DaveMy1978

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.

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

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