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
|