# 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 doesnt 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