That was just a stray line that got copied by accident.
--
Regards,
Tom Ogilvy
"Christy" <(E-Mail Removed)> wrote in message
news:ED77672B-9804-4E54-A780-(E-Mail Removed)...
> Thanks Tom! It works fine but it wouldn't let me use the line above the
> Sub
> ProcessCountHistory() line. I commented thaty out and it seems to work
> great.
> Did I need to put that line above all my subs in the module?
>
> "Tom Ogilvy" wrote:
>
>>
>> set rng1 = rng.offset(0,-3).Resize(,3)
>>
>> Sub ProcessCountHistory()
>> Dim rng as Range, rng1 as range, cell as Range
>>
>> set rng = Range(cells(7,15),cells(rows.count,15).End(xlup))
>>
>> On error Resume Next
>> set rng1 = rng.specialCells(xlBlanks)
>> On error goto 0
>>
>> if rng1 is nothing then exit sub
>>
>> For each cell in rng1
>> If Cell.Offset(-1, 0).Value = "YES" Then
>> If Cell.Offset(-2, 0).Value = "YES" Then
>> If Cell.Offset(-3, 0).Value = "YES" Then
>> Cell.Offset(0, -2).Value = "Count required?"
>> Cell.Value = "NO"
>> Else
>> Cell.Offset(0, -2).Value = "Count required?"
>> Cell.Value = "YES"
>> End If
>> Else
>> Cell.Offset(0, -2).Value = "Count required?"
>> Cell.Value = "YES"
>> End If
>> Else
>> Cell.Offset(0, -2).Value = "Count required?"
>> Cell.Value = "YES"
>>
>> End If
>> Next cell
>> Range("a1").Select
>> End Sub
>>
>> This assumes that the cells in column O are actually blank
>>
>> if the cells appear blank because there is a formula like
>> =if(condition,"",number result)
>>
>> so all non blank appearing cells are numeric you could change
>> xlblank to
>>
>> xlformulas,xltextvalues
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>> "Christy" wrote:
>>
>> > WOW - those last answers not only worked well but were som mcuh faster
>> > that
>> > my code - I LOVE this forum!!!
>> >
>> > Below is anouther loop I am using - it works but is slow and
>> > inefficient -
>> > any suggestions for improvement would be muxh appreciated. Not sure how
>> > to
>> > impliment the 'used range' and 'no selection' tips here.
>> >
>> > Sub ProcessCountHistory()
>> > Dim i As Integer
>> >
>> > For i = 7 To 7500
>> > Cells(i, 15).Select
>> > If ActiveCell.Value = "" Then
>> > If ActiveCell.Offset(-1, 0).Value = "YES" Then
>> > If ActiveCell.Offset(-2, 0).Value = "YES" Then
>> > If ActiveCell.Offset(-3, 0).Value = "YES" Then
>> > ActiveCell.Offset(0, -2).Value = "Count
>> > required?"
>> > ActiveCell.Value = "NO"
>> > Else
>> > ActiveCell.Offset(0, -2).Value = "Count
>> > required?"
>> > ActiveCell.Value = "YES"
>> > End If
>> > Else
>> > ActiveCell.Offset(0, -2).Value = "Count required?"
>> > ActiveCell.Value = "YES"
>> > End If
>> > Else
>> > ActiveCell.Offset(0, -2).Value = "Count required?"
>> > ActiveCell.Value = "YES"
>> > End If
>> > End If
>> > Next i
>> > Range("a1").Select
>> > End Sub
|
Similar Threads
