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

[sssb2000]

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?

 

[Legacy 12630]

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]

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]

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?

 

[Legacy 12630]

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

Does that make more sense?

No.

 

[sssb2000]

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

 

[Legacy 12630]

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

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]

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]

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.