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 !

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

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

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.

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

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

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","")

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

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

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

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 ?

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

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

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