Anouther loop question
 

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

Anouther loop question
 

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

Anouther loop question
 

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

Anouther loop question
 

That was just a stray line that got copied by accident.

--
Regards,
Tom Ogilvy


"Christy" wrote in message
...
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