| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| loop question in VBA | Excel Programming | |||
| The 7 nested If() function Limitation. Is there anouther way? | Excel Worksheet Functions | |||
| end with loop question | Excel Programming | |||
| copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
| In excel, importing from anouther sheet background colors will no. | Excel Worksheet Functions | |||
Linear Mode
