Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
I have pondered this for a while now. I cannot think of a proper
formula to do the job. Simply put - I have about 20,000 rows with 2 columns per set. I want to somehow identify when a number greater than zero first appears - at which point, I would like to denote it with an X in column A.. There would be a maximum of 30 columns in each set. The way it would search for the number per set, is to start in cell B1. If B1 is greater than zero, then X is to be place in. If not, it would go down to B2. If not again, then back up to C1.... then C2 right to a maximum of cell AE2. it will eventually encounter a number greater than zero. After it encounters is it would then go to the next set directly below which would be B3 and B4 etc.... Here are a few examples (I have shortened the columns to 7 for illustaration purposes only): A B C D E F G H X 0 0 0 0 2 3 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 X 0 0 1 0 0 1 0 X 0 0 0 0 0 0 1 0 0 0 0 0 0 0 X 0 3 0 3 1 0 0 0 2 0 1 0 0 0 0 0 0 0 0 0 0 X 1 0 0 3 3 0 0 X 0 0 1 1 1 0 0 0 0 0 0 0 0 0 Thanks in advance ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Try...
Sub DenoteCellsGreaterTanZero() Dim r For r = 1 To ActiveSheet.UsedRange.Rows.Count If Application.WorksheetFunction.CountIf(Rows(r), "0") 0 _ Then Cells(r, 1) = "x" Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
GS explained on 9/2/2011 :
Try... Sub DenoteCellsGreaterTanZero() Dim r For r = 1 To ActiveSheet.UsedRange.Rows.Count If Application.WorksheetFunction.CountIf(Rows(r), "0") 0 _ Then Cells(r, 1) = "x" Next 'r End Sub Note that this is a programming group! BUT Since you asked for a formula... =IF(COUNTIF(B1:AE1,"0"),"x","") -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 2, 3:52*pm, GS wrote:
GS explained on 9/2/2011 : Try... Sub DenoteCellsGreaterTanZero() * Dim r * For r = 1 To ActiveSheet.UsedRange.Rows.Count * * If Application.WorksheetFunction.CountIf(Rows(r), "0") 0 _ * * * *Then Cells(r, 1) = "x" * Next 'r End Sub Note that this is a programming group! BUT Since you asked for a formula... * =IF(COUNTIF(B1:AE1,"0"),"x","") -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, I tried both the formula and the macro that you created. Maybe I am doing something wrong, but it is always returning an "X" in every row as it will eventually find a number greater than zero. I don't think I was clear enough in my explanation. What I wanted was an "X" in either one row or another only once it encounters the number greater than zero. Therefore, only each set of two rows will contain an "X".... not both - and of course it must search in the pattern that I previouisly stipulated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan formulated on Friday :
On Sep 2, 3:52*pm, GS wrote: GS explained on 9/2/2011 : Try... Sub DenoteCellsGreaterTanZero() * Dim r * For r = 1 To ActiveSheet.UsedRange.Rows.Count * * If Application.WorksheetFunction.CountIf(Rows(r), "0") 0 _ * * * *Then Cells(r, 1) = "x" * Next 'r End Sub Note that this is a programming group! BUT Since you asked for a formula... * =IF(COUNTIF(B1:AE1,"0"),"x","") -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, I tried both the formula and the macro that you created. Maybe I am doing something wrong, but it is always returning an "X" in every row as it will eventually find a number greater than zero. I don't think I was clear enough in my explanation. What I wanted was an "X" in either one row or another only once it encounters the number greater than zero. Therefore, only each set of two rows will contain an "X".... not both - and of course it must search in the pattern that I previouisly stipulated. Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] Otherwise, put the x in the 2nd row.[?] Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) OR does the data start in Row2? Try... Sub DenoteCellsGreaterThanZero2() Dim r As Long Dim b1 As Boolean, b2 As Boolean For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 With Application.WorksheetFunction b1 = (.CountIf(Rows(r), "0") 0) b2 = (.CountIf(Rows(r).Offset(1), "0") 0) End With 'Application.WorksheetFunction If b1 Then Cells(r, 1) = "x" Else Cells(r, 1).Offset(1) = "x" Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Uh, here's a revision that takes into account neither row of a set has
a number 0... Sub DenoteCellsGreaterThanZero2() Dim r As Long Dim b1 As Boolean, b2 As Boolean For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 With Application.WorksheetFunction b1 = (.CountIf(Rows(r), "0") 0) b2 = (.CountIf(Rows(r).Offset(1), "0") 0) End With 'Application.WorksheetFunction If b1 Then Cells(r, 1) = "x": GoTo nextset If b2 Then Cells(r, 1).Offset(1) = "x" nextset: Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 2, 11:13*pm, GS wrote:
Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. Example: 00000174090 X 00004645000 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan brought next idea :
On Sep 2, 11:13*pm, GS wrote: Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. That's EXACTLY what the macro does!!! Example: 00000174090 X 00004645000 This example contradicts your explanation! CLEARLY, the 1st row contains a number 0 and so the code would put the x in that row and ignore the 2nd row. Example: 00000 00001 '//this row gets the x 00001 '//this row gets the x 11111 '//this row gets ignored because above row is 1st row 0 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 2, 2:16*pm, qcan wrote:
I have pondered this for a while now. I cannot think of a proper formula to do the job. Simply put - I have about 20,000 rows with 2 columns per set. I want to somehow identify when a number greater than zero first appears - at which point, I would like to denote it with an X in column A.. There would be a maximum of 30 columns in each set. The way it would search for the number per set, is to start in cell B1. If B1 is greater than zero, then X is to be place in. If not, it would go down to B2. If not again, then back up to C1.... then C2 right to a maximum of cell AE2. it will eventually encounter a number greater than zero. After it encounters is it would then go to the next set directly below which would be B3 and B4 etc.... Here are a few examples (I have shortened the columns to 7 for illustaration purposes only): A * * *B * * * *C * * * * * * * * D * * * * * * * * E F * * * * * * * * G * * * * * * * * H X * * *0 * * * *0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0 * * * * *0 * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * * *0 * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 X * * *0 * * * *0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0 X * * *0 * * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1 * * * * *0 * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 X * * *0 * * * *3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0 * * * * *0 * * *2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0 * * * * *0 * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 *X * * 1 * * * *0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0 *X * * 0 * * * *0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0 * * * * *0 * * *0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 Thanks in advance ! Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 3, 1:30*pm, GS wrote:
qcan brought next idea : On Sep 2, 11:13*pm, GS wrote: Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. That's EXACTLY what the macro does!!! Example: * 00000174090 X 00004645000 This example contradicts your explanation! CLEARLY, the 1st row contains a number 0 and so the code would put the x in that row and ignore the 2nd row. Example: * 00000 * 00001 *'//this row gets the x * 00001 *'//this row gets the x * 11111 *'//this row gets ignored because above row is 1st row 0 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Gary, No contradiction. I guess I am guilty of not being clear enough. As for your two examples. Yes, the first is correct. However, The second is not. The "X" would go to the second row. The search for the first number greater than zero works in a kind of a zig zag pattern. Keeping in mind that the search starts with B1 then (if it finds a zero) it goes DOWN to B2.. (if it again finds a zero) the search goes to C1..(if it again finds a zero) it goes to C2 and so on and so forth. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 3, 3:17*pm, qcan wrote:
On Sep 3, 1:30*pm, GS wrote: qcan brought next idea : On Sep 2, 11:13*pm, GS wrote: Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. That's EXACTLY what the macro does!!! Example: * 00000174090 X 00004645000 This example contradicts your explanation! CLEARLY, the 1st row contains a number 0 and so the code would put the x in that row and ignore the 2nd row. Example: * 00000 * 00001 *'//this row gets the x * 00001 *'//this row gets the x * 11111 *'//this row gets ignored because above row is 1st row 0 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Gary, No contradiction. I guess I am guilty of not being clear enough. As for your two examples. Yes, the first is correct. However, The second is not. The "X" would go to the second row. The search for the first number greater than zero works in a kind of a zig zag pattern. Keeping in mind that the search starts with B1 then (if it finds a zero) it goes DOWN to B2.. (if it again finds a zero) the search goes to C1..(if it again finds a zero) it goes to C2 and so on and so forth. Did you try what I offered? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan has brought this to us :
On Sep 3, 1:30*pm, GS wrote: qcan brought next idea : On Sep 2, 11:13*pm, GS wrote: Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. That's EXACTLY what the macro does!!! Example: * 00000174090 X 00004645000 This example contradicts your explanation! CLEARLY, the 1st row contains a number 0 and so the code would put the x in that row and ignore the 2nd row. Example: * 00000 * 00001 *'//this row gets the x * 00001 *'//this row gets the x * 11111 *'//this row gets ignored because above row is 1st row 0 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Gary, No contradiction. I guess I am guilty of not being clear enough. As for your two examples. Yes, the first is correct. However, The second is not. The "X" would go to the second row. The search for the first number greater than zero works in a kind of a zig zag pattern. Keeping in mind that the search starts with B1 then (if it finds a zero) it goes DOWN to B2.. (if it again finds a zero) the search goes to C1..(if it again finds a zero) it goes to C2 and so on and so forth. And this is exactly what the code does!!! It will only put an x in the 1st find <0 per pair. If no find then no x in either! Not sure what you're doing that it doesn't work that way for you but that's how it works here! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
typo...
And this is exactly what the code does!!! It will only put an x in the 1st find 0 per pair. If no find then no x in either! Not sure what you're doing that it doesn't work that way for you but that's how it works here! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan expressed precisely :
On Sep 3, 1:30*pm, GS wrote: qcan brought next idea : On Sep 2, 11:13*pm, GS wrote: Uh, here's a revision that takes into account neither row of a set has a number 0... Sub DenoteCellsGreaterThanZero2() * Dim r As Long * Dim b1 As Boolean, b2 As Boolean * For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 * * With Application.WorksheetFunction * * * b1 = (.CountIf(Rows(r), "0") 0) * * * b2 = (.CountIf(Rows(r).Offset(1), "0") 0) * * End With 'Application.WorksheetFunction * * If b1 Then Cells(r, 1) = "x": GoTo nextset * * If b2 Then Cells(r, 1).Offset(1) = "x" nextset: * Next 'r End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hey Gary, Close, but not quite. I tried your macro. The macro runs, but it is not returning the correct info. To answer your questions..... Ok, so you want it to place an x in the first row of the pair that it finds a number greater than zero, AND if the 1st row is that row then ignore the 2nd row.[?] *** Correct. The same applies if it encounters a number greater than zero for the first time in the second row, if it still has not encountered a number greater than zero in the previous first row. In other words an "X" will be placed in either the first row or second row - but never both. That's EXACTLY what the macro does!!! Example: * 00000174090 X 00004645000 This example contradicts your explanation! CLEARLY, the 1st row contains a number 0 and so the code would put the x in that row and ignore the 2nd row. Example: * 00000 * 00001 *'//this row gets the x * 00001 *'//this row gets the x * 11111 *'//this row gets ignored because above row is 1st row 0 Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) *** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6 etc.... -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Gary, No contradiction. I guess I am guilty of not being clear enough. As for your two examples. Yes, the first is correct. However, The second is not. The "X" would go to the second row. The search for the first number greater than zero works in a kind of a zig zag pattern. Keeping in mind that the search starts with B1 then (if it finds a zero) it goes DOWN to B2.. (if it again finds a zero) the search goes to C1..(if it again finds a zero) it goes to C2 and so on and so forth. The only possible reason the code could fail is if there's blank rows between the sets. (Not what you stated) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Here's the result of running the code on the sample data you posted...
x 0 0 0 0 2 3 0 '//1st 0 0 0 0 0 0 0 1 '//ignored -- 0 0 0 0 0 0 0 '//ignored x 0 0 1 0 0 1 0 '//1st 0 -- x 0 0 0 0 0 0 1 '//1st 0 0 0 0 0 0 0 0 '//ignored -- x 0 3 0 3 1 0 0 '//1st 0 0 2 0 1 0 0 0 '//ignored -- 0 0 0 0 0 0 0 '//ignored x 1 0 0 3 3 0 0 '//1st 0 -- x 0 0 1 1 1 0 0 '//1st 0 0 0 0 0 0 0 0 '//ignored ----'//test set 0 0 0 0 0 0 0 '//ignored 0 0 0 0 0 0 0 '//ignored ...as rendered by this code... Sub DenoteCellsGreaterThanZero2() Dim r As Long Dim b1 As Boolean, b2 As Boolean For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 With Application.WorksheetFunction b1 = (.CountIf(Rows(r), "0") 0) b2 = (.CountIf(Rows(r).Offset(1), "0") 0) End With 'Application.WorksheetFunction If b1 Then Cells(r, 1) = "x": GoTo nextset If b2 Then Cells(r, 1).Offset(1) = "x" nextset: Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Don Guillett used his keyboard to write :
Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 3, 4:51*pm, GS wrote:
Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Don Guillett explained on 9/3/2011 :
On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x 0 0 0 0 2 3 0_set1/row1 3-- 0 0 0 0 0 0 1_set1/row2 4-- 0 0 0 0 0 0 0_set2/row1 5-- x 0 0 1 0 0 1 0_set2/row2 6-- 0 0 0 0 0 0 1_set3/row1 '//no x here 7-- 0 0 0 0 0 0 0_set3/row2 8-- x 0 3 0 3 1 0 0_set4/row1 9-- 0 2 0 1 0 0 0_set4/row2 10- 0 0 0 0 0 0 0_set5/row1 11- x 1 0 0 3 3 0 0_set5/row2 12- 0 0 1 1 1 0 0_set6/row1 '//no x here 13- 0 0 0 0 0 0 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 3, 8:04*pm, GS wrote:
Don Guillett explained on 9/3/2011 : On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... * * A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x * 0 * * * 0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0_set1/row1 3-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set1/row2 4-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set2/row1 5-- x * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0_set2/row2 6-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set3/row1 *'//no x here 7-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set3/row2 8-- x * 0 * * * 3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0_set4/row1 9-- * * 0 * * * 2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0_set4/row2 10- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set5/row1 11- x * 1 * * * 0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0_set5/row2 12- * * 0 * * * 0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0_set6/row1 *'//no x here 13- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry / Don, Don, I tried your formula. Sorry, It does not work properly. Garry, With regards to above with the results that you get - All is correct on your small sample with the exception of set 3 and set 6. Why wouldn't an "X" be returned on both of these sets ? |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan has brought this to us :
On Sep 3, 8:04*pm, GS wrote: Don Guillett explained on 9/3/2011 : On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... * * A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x * 0 * * * 0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0_set1/row1 3-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set1/row2 4-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set2/row1 5-- x * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0_set2/row2 6-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set3/row1 *'//no x here 7-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set3/row2 8-- x * 0 * * * 3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0_set4/row1 9-- * * 0 * * * 2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0_set4/row2 10- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set5/row1 11- x * 1 * * * 0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0_set5/row2 12- * * 0 * * * 0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0_set6/row1 *'//no x here 13- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry / Don, Don, I tried your formula. Sorry, It does not work properly. Garry, With regards to above with the results that you get - All is correct on your small sample with the exception of set 3 and set 6. Why wouldn't an "X" be returned on both of these sets ? My sample DOES return an x for all 6 sets. Don's formula (return sample above) DOES NOT return an x for sets 3/6 (as I stated here at the top). Sounds to me like you're confused about who's/what return samples are being provided. My code returns an x for all 6 sets as noted in my reply (which also includes the macro I used to get those results)! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 12:54*am, GS wrote:
qcan has brought this to us : On Sep 3, 8:04*pm, GS wrote: Don Guillett explained on 9/3/2011 : On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... * * A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x * 0 * * * 0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0_set1/row1 3-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set1/row2 4-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set2/row1 5-- x * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0_set2/row2 6-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set3/row1 *'//no x here 7-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set3/row2 8-- x * 0 * * * 3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0_set4/row1 9-- * * 0 * * * 2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0_set4/row2 10- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set5/row1 11- x * 1 * * * 0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0_set5/row2 12- * * 0 * * * 0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0_set6/row1 *'//no x here 13- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry / Don, Don, I tried your formula. Sorry, It does not work properly. Garry, With regards to above with the results that you get - All is correct on your small sample with the exception of set 3 and set 6. Why wouldn't an "X" be returned on both of these sets ? My sample DOES return an x for all 6 sets. Don's formula (return sample above) DOES NOT return an x for sets 3/6 (as I stated here at the top). Sounds to me like you're confused about who's/what return samples are being provided. My code returns an x for all 6 sets as noted in my reply (which also includes the macro I used to get those results)! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry.... Based on the second sample I guess I misunderstood the setup as it appeared to me from the FIRST sample that the data sets were separated by a row as in 1:2 4:5 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 8:15*am, Don Guillett wrote:
On Sep 4, 12:54*am, GS wrote: qcan has brought this to us : On Sep 3, 8:04*pm, GS wrote: Don Guillett explained on 9/3/2011 : On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... * * A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x * 0 * * * 0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0_set1/row1 3-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set1/row2 4-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set2/row1 5-- x * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0_set2/row2 6-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set3/row1 *'//no x here 7-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set3/row2 8-- x * 0 * * * 3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0_set4/row1 9-- * * 0 * * * 2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0_set4/row2 10- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set5/row1 11- x * 1 * * * 0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0_set5/row2 12- * * 0 * * * 0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0_set6/row1 *'//no x here 13- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry / Don, Don, I tried your formula. Sorry, It does not work properly. Garry, With regards to above with the results that you get - All is correct on your small sample with the exception of set 3 and set 6. Why wouldn't an "X" be returned on both of these sets ? My sample DOES return an x for all 6 sets. Don's formula (return sample above) DOES NOT return an x for sets 3/6 (as I stated here at the top). Sounds to me like you're confused about who's/what return samples are being provided. My code returns an x for all 6 sets as noted in my reply (which also includes the macro I used to get those results)! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry.... Based on the second sample I guess I misunderstood the setup as it appeared to me from the FIRST sample that the data sets were separated by a row as in 1:2 4:5 Gary's seems to work. This macro does the same for whatever rows and columns for 1:2 3:4 etc Option Explicit Sub PlaceX() Dim lc As Long Dim i As Long lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Column' last column Columns(1).ClearContents For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row Step 2 If Application.CountIf(Range(Cells(i, 2), Cells(i, lc)), "0") Then Cells(i, 1) = "X" ElseIf Application.CountIf(Range(Cells(i + 1, 2), Cells(i + 1, lc)), "0") Then Cells(i + 1, 1) = "X" End If Next i End Sub |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 11:12*am, Don Guillett wrote:
On Sep 4, 8:15*am, Don Guillett wrote: On Sep 4, 12:54*am, GS wrote: qcan has brought this to us : On Sep 3, 8:04*pm, GS wrote: Don Guillett explained on 9/3/2011 : On Sep 3, 4:51*pm, GS wrote: Don Guillett used his keyboard to write : Insert a row at the top and put this in row2 and copy down =IF(AND(COUNTIF(B2:H2,""&0),a1<"x"),"x","") This does not place x for set#3 nor set#6 of the OP's sample data. It needs to test both rows to determine the action to take on one or the other. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Did you insert a row at the top. I can send a file or you can send me yours dguillett1 @gmail.com Yes, I inserted a row at the top as you instructed. Are you saying you get 1 x for each set of rows? Here are the results I get... * * A-- B-- C-- D-- E-- F-- G-- H 1-- 2-- x * 0 * * * 0 * * * 0 * * * 0 * * * 2 * * * 3 * * * 0_set1/row1 3-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set1/row2 4-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set2/row1 5-- x * 0 * * * 0 * * * 1 * * * 0 * * * 0 * * * 1 * * * 0_set2/row2 6-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 1_set3/row1 *'//no x here 7-- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set3/row2 8-- x * 0 * * * 3 * * * 0 * * * 3 * * * 1 * * * 0 * * * 0_set4/row1 9-- * * 0 * * * 2 * * * 0 * * * 1 * * * 0 * * * 0 * * * 0_set4/row2 10- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set5/row1 11- x * 1 * * * 0 * * * 0 * * * 3 * * * 3 * * * 0 * * * 0_set5/row2 12- * * 0 * * * 0 * * * 1 * * * 1 * * * 1 * * * 0 * * * 0_set6/row1 *'//no x here 13- * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0_set6/row2 Why do you think your file would be set up different than mine, since I set mine up as per your instructions? -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Garry / Don, Don, I tried your formula. Sorry, It does not work properly. Garry, With regards to above with the results that you get - All is correct on your small sample with the exception of set 3 and set 6. Why wouldn't an "X" be returned on both of these sets ? My sample DOES return an x for all 6 sets. Don's formula (return sample above) DOES NOT return an x for sets 3/6 (as I stated here at the top). Sounds to me like you're confused about who's/what return samples are being provided. My code returns an x for all 6 sets as noted in my reply (which also includes the macro I used to get those results)! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Sorry.... Based on the second sample I guess I misunderstood the setup as it appeared to me from the FIRST sample that the data sets were separated by a row as in 1:2 4:5 Gary's seems to work. This macro does the same for whatever rows and columns for 1:2 3:4 etc Option Explicit Sub PlaceX() Dim lc As Long Dim i As Long lc = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Column' last column Columns(1).ClearContents For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row Step 2 If Application.CountIf(Range(Cells(i, 2), Cells(i, lc)), "0") Then *Cells(i, 1) = "X" ElseIf Application.CountIf(Range(Cells(i + 1, 2), Cells(i + 1, lc)), "0") Then *Cells(i + 1, 1) = "X" End If Next i End Sub- Hide quoted text - - Show quoted text - Gary / Don, Yes, both of your macros return an "X" in each set. However, for whatever reason - it is always returning an "X" in the first row of each set, dispite the fact that the "X" should be in the second row in some cases when a number is greater than zero is first encountered. Not sure how to upload a file here. I will email Don a small sample spreadsheet on my data my data with both macros. Gary, what is you email address ? Thanks. |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Don Guillett formulated on Sunday :
Sorry.... Based on the second sample I guess I misunderstood the setup as it appeared to me from the FIRST sample that the data sets were separated by a row as in 1:2 4:5 Yes, I can see where you'd draw that conclusion. This is why I asked the OP about blank rows between sets. In that context, your formula works great!<g Thanks for your patient persistence... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
qcan wrote on 9/4/2011 :
Gary / Don, Yes, both of your macros return an "X" in each set. However, for whatever reason - it is always returning an "X" in the first row of each set, dispite the fact that the "X" should be in the second row in some cases when a number is greater than zero is first encountered. Not sure how to upload a file here. I will email Don a small sample spreadsheet on my data my data with both macros. Gary, what is you email address ? Thanks. According to my tests (and reported results), my macro ignores the 1st row of each set if there's no find 0. Thus, there are 2 sets that put x in the 2nd row only. I suggest you ALWAYS ClearContents between tests so you only see results for the test being done. IOW, if the macros don't behave EXACTLY the same then you'll see results from the previous test if you don't ClearContents beforehand. My email is gesansomATnetscapeDOTnet -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
GS was thinking very hard :
qcan wrote on 9/4/2011 : Gary / Don, Yes, both of your macros return an "X" in each set. However, for whatever reason - it is always returning an "X" in the first row of each set, dispite the fact that the "X" should be in the second row in some cases when a number is greater than zero is first encountered. Not sure how to upload a file here. I will email Don a small sample spreadsheet on my data my data with both macros. Gary, what is you email address ? Thanks. According to my tests (and reported results), my macro ignores the 1st row of each set if there's no find 0. Thus, there are 2 sets that put x in the 2nd row only. I suggest you ALWAYS ClearContents between tests so you only see results for the test being done. IOW, if the macros don't behave EXACTLY the same then you'll see results from the previous test if you don't ClearContents beforehand. My email is gesansomATnetscapeDOTnet According to my tests of Don's macro, it does EXACTLY the same thing as my macro. IOW, both macros ignore the 1st row of each set if there's no find 0! Here's a revision of my last macro that clears column 1 at the start (as Don's does)... Sub DenoteCellsGreaterThanZero2() Dim r As Long, bRow1 As Boolean, bRow2 As Boolean Columns(1).ClearContents For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2 With Application.WorksheetFunction bRow1 = (.CountIf(Rows(r), "0") 0) bRow2 = (.CountIf(Rows(r).Offset(1), "0") 0) End With 'Application.WorksheetFunction If bRow1 Then Cells(r, 1) = "x": GoTo nextset If bRow2 Then Cells(r, 1).Offset(1) = "x" nextset: Next 'r End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Ron,
Given the logic you detailed in your email, the bottom line is that ColA will contain an x based on the FINAL CHECK of the last column in each set. That precludes, then, than some sets will have x in both rows (which you clearly stated is NOT what you want). In this case, both macros need only be done on the last column to return the desired results. Thus, your sample file is NOT correct as sent because it places x in the 1st row of every set (as you stated). Using your logic as stated (checking every cell in both rows) will result in an x being on every row of every set because at some point checking every cell, an x is always placed in ColA for both rows BECAUSE your logic doesn't say to remove an x in the other row of the set being checked. If the last column determines the result in ColA for both rows of a set then there should only be 5 x's on your sample wks: Rows 11,14,17,20,26. Here's the code I used... Sub PlaceX2() Dim vTemp As Variant, lRow As Long, lCol As Long Dim bRow1 As Boolean, bRow2 As Boolean lCol = Cells.Find(What:="*", _ After:=Cells(Rows.Count, Columns.Count), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Column Columns(1).ClearContents For lRow = 1 To Cells(Rows.Count, 2).End(xlUp).Row Step 2 vTemp = Range(Cells(lRow, 2), Cells(lRow + 1, lCol)) bRow1 = (vTemp(1, lCol - 1) 0) bRow2 = (vTemp(2, lCol - 1) 0) If bRow1 Then Cells(lRow, 1) = "x": GoTo nextset If bRow2 Then Cells(lRow, 1).Offset(1) = "x" nextset: Next 'lRow End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 11:47*am, GS wrote:
qcan wrote on 9/4/2011 : Gary / Don, Yes, both of your macros return an "X" in each set. However, for whatever reason - it is always returning an "X" in the first row of each set, dispite the fact that the "X" should be in the second row in some cases when a number is greater than zero is first encountered. Not sure how to upload a file here. I will email Don a small sample spreadsheet on my data my data with both macros. Gary, what is you email address ? Thanks. According to my tests (and reported results), my macro ignores the 1st row of each set if there's no find 0. Thus, there are 2 sets that put x in the 2nd row only. I suggest you ALWAYS ClearContents between tests so you only see results for the test being done. IOW, if the macros don't behave EXACTLY the same then you'll see results from the previous test if you don't ClearContents beforehand. My email is gesansomATnetscapeDOTnet -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc OK. I think I may finally understand what is needed. Option Explicit Sub findgreaterzeroinblock() Dim lr As Long Dim i As Long Dim j As Long Dim lc As Long Application.ScreenUpdating = False Columns(1).ClearContents lr = Cells(Rows.Count, 2).End(xlUp).Row lc = Cells.Find("*", Cells(Rows.Count, _ Columns.Count), , , xlByRows, xlPrevious).Column For i = 1 To lr Step 2 For j = 2 To lc If Cells(i, j) 0 Then Cells(i, 1) = "1" Exit For ElseIf Cells(i + 1, j) 0 Then Cells(i + 1, 1) = "2" Exit For End If Next j Next i Application.ScreenUpdating = True End Sub |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Don,
What I find interesting is the different interpretations we derive from reading the same email. Your solution is correct so long as Ron wants to abort checking any further once the first 0 is found. Although he does not state this, I'm reconsidering that you may indeed have correctly understood the task at hand, whereas I'm out in left field somewhere on this! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 2:58*pm, GS wrote:
Don, What I find interesting is the different interpretations we derive from reading the same email. Your solution is correct so long as Ron wants to abort checking any further once the first 0 is found. Although he does not state this, I'm reconsidering that you may indeed have correctly understood the task at hand, whereas I'm out in left field somewhere on this! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Yes, Don indeed nailed it. Yes, I wanted to abort checking any further once the first 0 was found and yes, I am guilty of not mentioning this as I assumed (incorrectly?) that it was not important. THANKS again Gary for all your effort... and again THANK you Don ! |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
To follow Don's understanding of your task...
Sub PlaceX3() Dim vTemp As Variant Dim lRow As Long, lCol As Long, j As Long lCol = Cells.Find(What:="*", _ After:=Cells(Rows.Count, Columns.Count), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Column Columns(1).ClearContents For lRow = 1 To Cells(Rows.Count, 2).End(xlUp).Row Step 2 vTemp = Range(Cells(lRow, 2), Cells(lRow + 1, lCol)) For j = 1 To lCol - 1 If vTemp(1, j) 0 Then Cells(lRow, 1) = "X": GoTo nextset ElseIf vTemp(2, j) 0 Then Cells(lRow, 1).Offset(1) = "X": GoTo nextset End If Next 'j nextset: Next 'lRow End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
After serious thinking qcan wrote :
On Sep 4, 2:58*pm, GS wrote: Don, What I find interesting is the different interpretations we derive from reading the same email. Your solution is correct so long as Ron wants to abort checking any further once the first 0 is found. Although he does not state this, I'm reconsidering that you may indeed have correctly understood the task at hand, whereas I'm out in left field somewhere on this! -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Garry, Yes, Don indeed nailed it. Yes, I wanted to abort checking any further once the first 0 was found and yes, I am guilty of not mentioning this as I assumed (incorrectly?) that it was not important. THANKS again Gary for all your effort... and again THANK you Don ! Thanks, Don, for confirming that! See my outer posts for my version of Don's understanding. Best wishes... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
On Sep 4, 11:47*am, GS wrote:
qcan wrote on 9/4/2011 : Gary / Don, Yes, both of your macros return an "X" in each set. However, for whatever reason - it is always returning an "X" in the first row of each set, dispite the fact that the "X" should be in the second row in some cases when a number is greater than zero is first encountered. Not sure how to upload a file here. I will email Don a small sample spreadsheet on my data my data with both macros. Gary, what is you email address ? Thanks. According to my tests (and reported results), my macro ignores the 1st row of each set if there's no find 0. Thus, there are 2 sets that put x in the 2nd row only. I suggest you ALWAYS ClearContents between tests so you only see results for the test being done. IOW, if the macros don't behave EXACTLY the same then you'll see results from the previous test if you don't ClearContents beforehand. My email is gesansomATnetscapeDOTnet -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thought I posted this. OP tells me it worked as desired. Option Explicit Sub findgreaterzeroinblock() Dim lr As Long Dim i As Long Dim j As Long Dim lc As Long Application.ScreenUpdating = False Columns(1).ClearContents lr = Cells(Rows.Count, 2).End(xlUp).Row lc = Cells.Find("*", Cells(Rows.Count, _ Columns.Count), , , xlByRows, xlPrevious).Column For i = 1 To lr Step 2 For j = 2 To lc If Cells(i, j) 0 Then Cells(i, 1) = "1" Exit For ElseIf Cells(i + 1, j) 0 Then Cells(i + 1, 1) = "2" Exit For End If Next j Next i Application.ScreenUpdating = True End Sub |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Yes, Don, I got that and tested it. See my outer posts for my version
of your understanding of the task. Your macro as I tested it... Sub findgreaterzeroinblock() Dim lr As Long, i As Long, j As Long, lc As Long Application.ScreenUpdating = False Columns(1).ClearContents lr = Cells(Rows.Count, 2).End(xlUp).Row lc = Cells.Find("*", Cells(Rows.Count, _ Columns.Count), , , xlByRows, xlPrevious).Column For i = 1 To lr Step 2 For j = 2 To lc If Cells(i, j) 0 Then Cells(i, 1) = "1": Exit For ElseIf Cells(i + 1, j) 0 Then Cells(i + 1, 1) = "2": Exit For End If Next j Next i Application.ScreenUpdating = True End Sub Works great! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking For A Formula
Ron,
In my previous example, it doesn't work very fast if processing thousands of rows because while it reads the entire set in one step, it writes the output range each iteration of the inner loop that finds 0. To speed the process up I revised the macro to read/write the ranges in one step each so the entire process is done in memory before writing back to the wks, as follows: Sub FindFirstCellGreaterThanZero2() ' Finds the 1st cell that contains 0 in a set of row pairs Dim vTemp As Variant, vResults() As String Dim lRow As Long, lCol As Long, j As Long, r As Long lCol = Cells.Find(What:="*", _ After:=Cells(Rows.Count, Columns.Count), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Column lRow = Cells(Rows.Count, 2).End(xlUp).Row ReDim vResults(0, lRow) Columns(1).ClearContents Application.ScreenUpdating = False For r = 1 To lRow Step 2 vTemp = Range(Cells(r, 2), Cells(r + 1, lCol)) For j = 1 To lCol - 1 If vTemp(1, j) 0 Then vResults(0, r - 1) = "X": GoTo nextset ElseIf vTemp(2, j) 0 Then vResults(0, r) = "X": GoTo nextset End If Next 'j nextset: Next 'r Range("A1").Resize(lRow, 1) = _ Application.WorksheetFunction.Transpose(vResults) Application.ScreenUpdating = True End Sub Also, I added the following ConditionalFormatting concept (by Chip Pearson) to shade every other pair of rows 'light green'. Select the range to be evaluated (in this case "A1:AE30") Add CF formula: =MOD(ROW()-Rw,N*2)+1<=N Set the desired color for row shading The above formula starts shading in 'odd' sets (ie: 1st,3rd,...). If you want the shaded sets to start 'even' (ie: 2nd,4th,...), use the following formula instead. =MOD(ROW()-Rw,N*2)+1N Note that in the above formulas you need to replace the placeholders Rw and N with your values as follows: Rw: The 1st row number to begin shading. N: The number of consecutive rows to shade. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel Formula Guidance. Formula need to determine if cell is popul | Excel Programming | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |