A1:A20 has quantity of personnel
B1:B20 has their titles
C1:C20 has is blank

I need to know how to put a Yes on 75% of these people and 25% No via VBA on
range C1:C20.

Always, appreciate your help,
Adnan

Sub adnan()
Range("C1:C15").Value = "Yes"
Range("C16:C20").Value = "No"
End Sub

--
Gary''s Student - gsnu200799

Gary,

Numbers in QTY varies, a cell could have 10 people, and the ranges varies,
too (it could be A1:A5) etc€¦

Need something that sums the qty and multiplies with 0.75 and then put the
Yes or No results (75% Yes, 25% No) range C1 thru the last row.

Im not sure how to even start€¦
Thanks for prompt response Gary!



"Gary''s Student" wrote:

Sub adnan()
Range("C1:C15").Value = "Yes"
Range("C16:C20").Value = "No"
End Sub

--
Gary''s Student - gsnu200799

And you want this to be independent of the actual number of rows, right? I
mean, you want it to work whether the rows run A1:A20 or A1:A37?

Well, let's start he There are a number of ways of getting a program to
determine the extent of a range, but if it'll work the one I think simplest
is the .End property of a range. Let's assume that your rows start in row 1
and that there's at least a blank line after the end of that range; in that
case you can find the last row this way:

LastRow = YourSheet.Cells(1, 1).End(xlDown).Row

Cells(1, 1) is synonymous with Range("A1"), of course, and .End(xlDown)
returns the same cell that would be activated if you focused on A1, then hit
<End, <Down. That gets you the cell; I tacked on .Row because all we need
is the row number, not the cell itself.

Your code can then add up the quantities in A1:A<LastRow either by looping
or by using the Sum worksheet function:

SumPerson = 0
For ir = 1 to LastRow
SumPerson = SumPerson + YourSheet.Cells(ir, 1).Value
Next ir

....or just

SumPerson = Application.WorksheetFunction.Sum(Range("A1:A"&Las tRow))

I think WorksheetFunction.Sum is better because it already knows how to
ignore non-numeric cells etc; your code could bomb if you had an unexpected
value in one of the QT cells. (Or maybe that's an advantage.)

Ok, now you have the sum of the QT column. If you don't care WHICH of the
rows get Yesses and Nos, you can just got down the list assigning Yes until
you pass 75%, like this:

SumPerson = SumPerson * 0.75
For ir = 1 to LastRow
if SumPerson 0 then vx = "Yes" Else vx = "No"
YourSheet.Cells(ir, 3) = vx
SumPerson = SumPerson - YourSheet.Cells(ir, 1)
Next ir

This way you take away the count of Yes personnel from SumPerson until
SumPerson is entirely depleted, and then quit the loop. The disadvantage to
this method - if you regard it as a disadvantage - is that Yes will end up
being assigned to somewhat more than 75% of the personnel, unless it just
happens to come out even. But then you cannot be sure of it being exact even
if you do it manually, so you probably needed it to be approximate only. If
you need it to be as close as possible, you'll have to get a little fancier,
starting with a sort of the rows on column 1 descending. But this is enough
to get you started; if you need to get fancier, you can always ask again.

--- "Adnan" wrote:
A1:A20 has quantity of personnel
B1:B20 has their titles
C1:C20 has is blank

I need to know how to put a Yes on 75% of these people and 25% No via VBA on
range C1:C20.

Bob,

I appreciate your nice detailed explanation. With the help you provided you
got me almost squared away. Just these two little things would fix the issue:

One, the range is fixed range (A1:A20)
This 75% should reflect or be based on quantity of titles not number of rows.

Heres an example of how it should look:

A B C D
1 10 Title 1 No
2 20 Title 2 Yes
3 1 Title 3 Yes
4 Title 4 No
5 Title 5 No
6 1 Title 6 Yes
7 Title 6 No
8 3 Title 6 Yes
11 1 Title 6 No
12 10 Title 6 No
13 1 Title 6 No
9 Title 6 No
10 50 Title 6 Yes
11 1 Title 6 No
12 1 Title 6 No
13 1 Title 6 No

Again, thank you!
Adnan

"Bob Bridges" wrote:

And you want this to be independent of the actual number of rows, right? I
mean, you want it to work whether the rows run A1:A20 or A1:A37?

Well, let's start he There are a number of ways of getting a program to
determine the extent of a range, but if it'll work the one I think simplest
is the .End property of a range. Let's assume that your rows start in row 1
and that there's at least a blank line after the end of that range; in that
case you can find the last row this way:

LastRow = YourSheet.Cells(1, 1).End(xlDown).Row

Cells(1, 1) is synonymous with Range("A1"), of course, and .End(xlDown)
returns the same cell that would be activated if you focused on A1, then hit
<End, <Down. That gets you the cell; I tacked on .Row because all we need
is the row number, not the cell itself.

Your code can then add up the quantities in A1:A<LastRow either by looping
or by using the Sum worksheet function:

SumPerson = 0
For ir = 1 to LastRow
SumPerson = SumPerson + YourSheet.Cells(ir, 1).Value
Next ir

...or just

SumPerson = Application.WorksheetFunction.Sum(Range("A1:A"&Las tRow))

I think WorksheetFunction.Sum is better because it already knows how to
ignore non-numeric cells etc; your code could bomb if you had an unexpected
value in one of the QT cells. (Or maybe that's an advantage.)

Ok, now you have the sum of the QT column. If you don't care WHICH of the
rows get Yesses and Nos, you can just got down the list assigning Yes until
you pass 75%, like this:

SumPerson = SumPerson * 0.75
For ir = 1 to LastRow
if SumPerson 0 then vx = "Yes" Else vx = "No"
YourSheet.Cells(ir, 3) = vx
SumPerson = SumPerson - YourSheet.Cells(ir, 1)
Next ir

This way you take away the count of Yes personnel from SumPerson until
SumPerson is entirely depleted, and then quit the loop. The disadvantage to
this method - if you regard it as a disadvantage - is that Yes will end up
being assigned to somewhat more than 75% of the personnel, unless it just
happens to come out even. But then you cannot be sure of it being exact even
if you do it manually, so you probably needed it to be approximate only. If
you need it to be as close as possible, you'll have to get a little fancier,
starting with a sort of the rows on column 1 descending. But this is enough
to get you started; if you need to get fancier, you can always ask again.

--- "Adnan" wrote:
A1:A20 has quantity of personnel
B1:B20 has their titles
C1:C20 has is blank

I need to know how to put a Yes on 75% of these people and 25% No via VBA on
range C1:C20.

Adnan: One, the range is fixed range (A1:A20)

No, you told Gary's Student "the ranges varies (it could be A1:A5)". I'll
ignore this part until you tell me what you mean.

Adnan: This 75% should reflect or be based on quantity of titles not number
of rows.

Ok, but I don't know what you mean. The "quantity of titles"? In 20 rows
there are no doubt 20 titles. If the 75% figure varies with some
characteristic of the title column (which you've moved to C in the latest
example, if I'm not mistaken?), you'll have to say HOW it varies...?

--- "Adnan" wrote:
I appreciate your nice detailed explanation. With the help you provided you
got me almost squared away. Just these two little things would fix the issue:

One, the range is fixed range (A1:A20)

This 75% should reflect or be based on quantity of titles not number of rows.

Heres an example of how it should look:
A B C D
1 10 Title 1 No
2 20 Title 2 Yes
3 1 Title 3 Yes
4 Title 4 No
5 Title 5 No
6 1 Title 6 Yes
7 Title 6 No
8 3 Title 6 Yes
11 1 Title 6 No
12 10 Title 6 No
13 1 Title 6 No
9 Title 6 No
10 50 Title 6 Yes
11 1 Title 6 No
12 1 Title 6 No
13 1 Title 6 No

"Bob Bridges" wrote:
And you want this to be independent of the actual number of rows, right? I
mean, you want it to work whether the rows run A1:A20 or A1:A37?

Well, let's start he There are a number of ways of getting a program to
determine the extent of a range, but if it'll work the one I think simplest
is the .End property of a range. Let's assume that your rows start in row 1
and that there's at least a blank line after the end of that range; in that
case you can find the last row this way:

LastRow = YourSheet.Cells(1, 1).End(xlDown).Row

Cells(1, 1) is synonymous with Range("A1"), of course, and .End(xlDown)
returns the same cell that would be activated if you focused on A1, then hit
<End, <Down. That gets you the cell; I tacked on .Row because all we
need is the row number, not the cell itself.

Your code can then add up the quantities in A1:A<LastRow either by looping
or by using the Sum worksheet function:

SumPerson = 0
For ir = 1 to LastRow
SumPerson = SumPerson + YourSheet.Cells(ir, 1).Value
Next ir

...or just

SumPerson = Application.WorksheetFunction.Sum(Range("A1:A"&Las tRow))

I think WorksheetFunction.Sum is better because it already knows how to
ignore non-numeric cells etc; your code could bomb if you had an unexpected
value in one of the QT cells. (Or maybe that's an advantage.)

Ok, now you have the sum of the QT column. If you don't care WHICH of the
rows get Yesses and Nos, you can just got down the list assigning Yes until
you pass 75%, like this:

SumPerson = SumPerson * 0.75
For ir = 1 to LastRow
if SumPerson 0 then vx = "Yes" Else vx = "No"
YourSheet.Cells(ir, 3) = vx
SumPerson = SumPerson - YourSheet.Cells(ir, 1)
Next ir

This way you take away the count of Yes personnel from SumPerson until
SumPerson is entirely depleted, and then quit the loop. The disadvantage to
this method - if you regard it as a disadvantage - is that Yes will end up
being assigned to somewhat more than 75% of the personnel, unless it just
happens to come out even. But then you cannot be sure of it being exact even
if you do it manually, so you probably needed it to be approximate only. If
you need it to be as close as possible, you'll have to get a little fancier,
starting with a sort of the rows on column 1 descending. But this is enough
to get you started; if you need to get fancier, you can always ask again.

--- "Adnan" wrote:
A1:A20 has quantity of personnel
B1:B20 has their titles
C1:C20 has is blank

I need to know how to put a Yes on 75% of these people and 25% No via
VBA on range C1:C20.

---Adnan: One, the range is fixed range (A1:A20)
**True.

---No, you told Gary's Student "the ranges varies (it could be A1:A5)". I'll
ignore this part until you tell me what you mean.
**Correct, ranges varies but I can set them to a fixed range since your code
does not skip blank lines and I have blanks lines.


---Adnan: This 75% should reflect or be based on quantity of titles not
number
of rows.
**Great, just what I need. But then the Yesses and Nos are being assigned a
little different. I need 75% of those titels to have Yes and the remainder No.

---Ok, but I don't know what you mean. The "quantity of titles"? In 20 rows
there are no doubt 20 titles. If the 75% figure varies with some
characteristic of the title column (which you've moved to C in the latest
example, if I'm not mistaken?), you'll have to say HOW it varies...?
**Like you said on the first post. I think we need to sort first and then
assign Yesses to 75% of those titles. Perhaps something that sums all those
qty titles, and say we have 100 of them in 60 rows, then assign Yesses to 75
of them, id does not matter what row, just cover 75% with Yes and the rest
with No.

Hre's what I did though, I added this sorting code and it looks like it
works, but the thing is now, can I restore the previous serting order?

This is the code I used, and this is the file sample:
http://cid-642741f4bfb02015.skydrive.live.com/self.aspx/Public/Bob|_s|_Help.xls

Sub RunCode()

' Sort rows first
Rows("1:20").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select


' Bob's code
LastRow = Sheet1.Cells(1, 1).End(xlDown).Row
SumPerson = 0
For ir = 1 To LastRow
SumPerson = SumPerson + Sheet1.Cells(ir, 1).Value
Next ir

SumPerson = SumPerson * 0.7
For ir = 1 To LastRow
If SumPerson 0 Then vx = "Yes" Else vx = "No"
Sheet1.Cells(ir, 3) = vx
SumPerson = SumPerson - Sheet1.Cells(ir, 1)
Next ir

End Sub



Bob, I thank you for the help,
Adnan


--- "Adnan" wrote:
I appreciate your nice detailed explanation. With the help you provided you
got me almost squared away. Just these two little things would fix the issue:

One, the range is fixed range (A1:A20)

This 75% should reflect or be based on quantity of titles not number of rows.

Heres an example of how it should look:
A B C D
1 10 Title 1 No
2 20 Title 2 Yes
3 1 Title 3 Yes
4 Title 4 No
5 Title 5 No
6 1 Title 6 Yes
7 Title 6 No
8 3 Title 6 Yes
11 1 Title 6 No
12 10 Title 6 No
13 1 Title 6 No
9 Title 6 No
10 50 Title 6 Yes
11 1 Title 6 No
12 1 Title 6 No
13 1 Title 6 No

"Bob Bridges" wrote:
And you want this to be independent of the actual number of rows, right? I
mean, you want it to work whether the rows run A1:A20 or A1:A37?

Well, let's start he There are a number of ways of getting a program to
determine the extent of a range, but if it'll work the one I think simplest
is the .End property of a range. Let's assume that your rows start in row 1
and that there's at least a blank line after the end of that range; in that
case you can find the last row this way:

LastRow = YourSheet.Cells(1, 1).End(xlDown).Row

Cells(1, 1) is synonymous with Range("A1"), of course, and .End(xlDown)
returns the same cell that would be activated if you focused on A1, then hit
<End, <Down. That gets you the cell; I tacked on .Row because all we
need is the row number, not the cell itself.

Your code can then add up the quantities in A1:A<LastRow either by looping
or by using the Sum worksheet function:

SumPerson = 0
For ir = 1 to LastRow
SumPerson = SumPerson + YourSheet.Cells(ir, 1).Value
Next ir

...or just

SumPerson = Application.WorksheetFunction.Sum(Range("A1:A"&Las tRow))

I think WorksheetFunction.Sum is better because it already knows how to
ignore non-numeric cells etc; your code could bomb if you had an unexpected
value in one of the QT cells. (Or maybe that's an advantage.)

Ok, now you have the sum of the QT column. If you don't care WHICH of the
rows get Yesses and Nos, you can just got down the list assigning Yes until
you pass 75%, like this:

SumPerson = SumPerson * 0.75
For ir = 1 to LastRow
if SumPerson 0 then vx = "Yes" Else vx = "No"
YourSheet.Cells(ir, 3) = vx
SumPerson = SumPerson - YourSheet.Cells(ir, 1)
Next ir

This way you take away the count of Yes personnel from SumPerson until
SumPerson is entirely depleted, and then quit the loop. The disadvantage to
this method - if you regard it as a disadvantage - is that Yes will end up
being assigned to somewhat more than 75% of the personnel, unless it just
happens to come out even. But then you cannot be sure of it being exact even
if you do it manually, so you probably needed it to be approximate only. If
you need it to be as close as possible, you'll have to get a little fancier,
starting with a sort of the rows on column 1 descending. But this is enough
to get you started; if you need to get fancier, you can always ask again.

--- "Adnan" wrote:
A1:A20 has quantity of personnel
B1:B20 has their titles
C1:C20 has is blank

I need to know how to put a Yes on 75% of these people and 25% No via
VBA on range C1:C20.

I don't think you can restore a previous sorting order, per se, but you can
certainly resort it so it'll be back in the previous order. Two ways:

1) If you can just sort by whatever columns it was sorted on in the first
place, that's easiest, of course. Was it perhaps sorted by title, before?

2) If the data themselves don't tell you exactly how to sort it back in the
previous order, and you need that exact order, then what you have to do is
add a temporary column before sorting it the first time. There are several
ways to do it but the simplest might be like this:

With YourSheet.Range("F1:F20")
.Formula = "=ROW()"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

(This looks more complicated than looping through F1:F20 and putting the row
number in each cell, but the latter method can be much slower if you're going
to do it to more than 20 rows or so; this is fast.)

Once you have this column you can continue with your sort on QT descending,
or whatever, and once you've done what you want you can sort on column F to
get it back in the original order, then delete that column.

--- "Adnan" wrote:
....Hre's what I did though, I added this sorting code and it looks like it
works, but the thing is now, can I restore the previous serting order?

Bob,

You have no idea how much youve helped. Its not just the code, but its
the advice/thought and ideas (especially the last one). Every response of
yours had the exact answers and I appreciate all this help.

God bless you,



"Bob Bridges" wrote:

I don't think you can restore a previous sorting order, per se, but you can
certainly resort it so it'll be back in the previous order. Two ways:

1) If you can just sort by whatever columns it was sorted on in the first
place, that's easiest, of course. Was it perhaps sorted by title, before?

2) If the data themselves don't tell you exactly how to sort it back in the
previous order, and you need that exact order, then what you have to do is
add a temporary column before sorting it the first time. There are several
ways to do it but the simplest might be like this:

With YourSheet.Range("F1:F20")
.Formula = "=ROW()"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

(This looks more complicated than looping through F1:F20 and putting the row
number in each cell, but the latter method can be much slower if you're going
to do it to more than 20 rows or so; this is fast.)

Once you have this column you can continue with your sort on QT descending,
or whatever, and once you've done what you want you can sort on column F to
get it back in the original order, then delete that column.

--- "Adnan" wrote:
....Hre's what I did though, I added this sorting code and it looks like it
works, but the thing is now, can I restore the previous serting order?