MrExcel.com - Your One Stop for Excel Tips & Solutions

Code for "14 points in a row Alternating"

Posted by Paul Magruder on April 20, 2001 6:50 AM

Is ther code that will allow me to launch a Macro(Macro1)if there is altermating results in a range? example: range ("B1:B14:). b1=2,b2=1, b3=2,b4=1 and so on... I need to launch a macro there are 14 1-2's or 2-1's in a row. If there is a skip in the process... then nothing.

Any suggestions would be greatly appreciated!

Thanks
Paul

Posted by Dave Hawley on April 20, 2001 7:06 AM


Hi Paul

There would probably be a few ways to do this, here is but one!

Sub TryThis()
Dim i As Integer
Dim myCell As Range

If WorksheetFunction.CountIf(Range("B1:B14"), 1) _
+ WorksheetFunction.CountIf(Range("B1:B14"), 2) = 14 Then
'Run "MyMacro
End If

For Each myCell In Range("B1:B13")
If myCell = 1 And myCell.Offset(1, 0) = 1 Then
'Run "MyMacro
Exit For
End If
Next i

End Sub

I generally avoid using Loops (to slloooowww) but if the Loop is less than 150 or so then I would.

Dave


OzGrid Business Applications

Posted by Dave Hawley on April 20, 2001 7:08 AM


Best change that first bit to include "Exit Sub' else "MyMacro' Could run twice:


If WorksheetFunction.CountIf(Range("B1:B14"), 1) _
+ WorksheetFunction.CountIf(Range("B1:B14"), 2) = 14 Then
'Run "MyMacro
Exit Sub
End If


Dave

OzGrid Business Applications

Posted by Dave Hawley on April 20, 2001 7:12 AM

Let's start again, I'm all thumbs tonight!


Sub TryThis()
Dim i As Integer
Dim myCell As Range

If WorksheetFunction.CountIf(Range("B1:B14"), 1) _
+ WorksheetFunction.CountIf(Range("B1:B14"), 2) = 14 Then
'Run "MyMacro
Exit Sub
End If

For Each myCell In Range("B1:B13")
If IsNumeric(myCell) Then
If myCell = 1 And myCell.Offset(1, 0) = 1 Then
'Run "MyMacro
Exit For
End If
End If
Next myCell

End Sub
OzGrid Business Applications

Posted by Paul Magruder on April 20, 2001 7:21 AM

Re: Forgot to Mention...


This time b1:b14... next time b2:b15.... Sorry....

Paul Sub TryThis()

Posted by Dave Hawley on April 20, 2001 7:38 AM

Re: Forgot to Mention...

Could you use a dynamic named range as shown on my Website ?

DaveOzGrid Business Applications

Posted by Dave Hawley on April 20, 2001 7:42 AM

Re: Forgot to Mention...

Could you use a dynamic named range as shown on my Website ?

Dave


....Or alternatively try this:


Sub TryThis()
Dim i As Integer
Dim myCell As Range
Dim LBottomRw As Long
Dim RCheck As Range


LBottomRw = Range("B65536").End(xlUp).Row

Set RCheck = Range("B1:B" & LBottomRw)
If WorksheetFunction.CountIf(RCheck, 1) _
+ WorksheetFunction.CountIf(RCheck, 2) = 14 Then
Set RCheck = Nothing
'Run "MyMacro
Exit Sub
End If

For Each myCell In RCheck
If IsNumeric(myCell) Then
If myCell = 1 And myCell.Offset(1, 0) = 1 Then
Set RCheck = Nothing
'Run "MyMacro
Exit For
End If
End If
Next myCell

End Sub

Dave

OzGrid Business Applications

Posted by Paul Magruder on April 20, 2001 9:49 AM

Re: Forgot to Mention...

Dave,First ... Thanks
The Macro works, except it doesn’t care if the numbers are alternating. If I have 5 2"S in a column and 9 1's in a in the column, it still see's 14 numbers, and runs my macro. If the numbers are not in alternating order, I need it to disregard...... Hope I'm Not getting on your nerves...

Thanks
Paul

Posted by Dave Hawley on April 20, 2001 9:57 AM

Re: Forgot to Mention...

No problem Paul, I actually thought you wanted that. but as you don't try this:


Sub TryThis()
Dim i As Integer
Dim myCell As Range

If WorksheetFunction.CountIf(Range("B1:B14"), 1) = 14 _
Or WorksheetFunction.CountIf(Range("B1:B14"), 2) = 14 Then
'Run "MyMacro
Exit Sub
End If

For Each myCell In Range("B1:B13")
If IsNumeric(myCell) Then
If myCell = 1 And myCell.Offset(1, 0) = 1 Then
'Run "MyMacro
Exit For
End If
End If
Next myCell

End Sub

OzGrid Business Applications

Posted by Paul Magruder on April 20, 2001 11:20 AM

Re: Forgot to Mention...

Dave,
Back to the moving range problem.....Went to your web site, Copied the formula for #1 in Dynamic named ranges, but don't know how to tell it to "Move" the whole range down 1 cell...Not "Expand".
Thanks
Paul

,First ... Thanks


Posted by Dave Hawley on April 20, 2001 11:33 AM

Try......

Paul, either of these 2 ways:

=OFFSET($A$2,0,0,COUNT($A$2:$A$65536),1)

...Or


=OFFSET($A$1,1,0,COUNT($A:$A),1)

Is that what you mean ?

Dave


OzGrid Business Applications

Posted by Paul Magruder on April 20, 2001 11:45 AM

Re: Try......

Dave
Not quite...
The first time I run the macro I need range A1:A14 (data ends at A14)
Then I enter data into A15... The second time I run the macro I need range A2:A15... and so on....

Thanks
Paul Paul, either of these 2 ways:

Posted by Dave Hawley on April 20, 2001 12:19 PM

Re: Try......

Paul, this is what dynamic ranges are for. If you named you range "MyRange" you would use this:


Sub TryThis()
Dim i As Integer
Dim myCell As Range

If WorksheetFunction.CountIf(Range("MyRange"), 1) = 14 _
Or WorksheetFunction.CountIf(Range("MyRange"), 2) = 14 Then
'Run "MyMacro
Exit Sub
End If

For Each myCell In Range("MyRange")
If IsNumeric(myCell) Then
If myCell = 1 And myCell.Offset(1, 0) = 1 Then
'Run "MyMacro
Exit For
End If
End If
Next myCell


Dave


OzGrid Business Applications