Macro for matching consecutive rows

[c0087]

The cells in A1:A5 are dynamic and change often, but C1:F10 are always the same.
I'm looking to run a macro that will stop when the values in A1:A5 match a 5 row pattern that's in C1:F10.
For example (1 2 3 4 5) in A1:A5 does not match anything in C1:F10. I would run the macro, and it wouldnt stop until A1:A5 was (1 5 9 13 17) or (11 15 19 23 27) or (4 8 12 16 20), etc, etc.
I have a trigger already

1		1	2	3	4
2		5	6	7	8
3		9	10	11	12
4		13	14	15	16
5		17	18	19	20
		21	22	23	24
		25	26	27	28
		29	30	31	32
		33	34	35	36
		37	38	39	40

 

[kvsrinivasamurthy]

Macro code

Sub RangeTally()
Dim K As Long, Ro As Long, Clm As Long
For Clm = 0 To 3
    For Ro = 0 To 5
    K = Evaluate("=SUM(IF(A1:A5-" & Range("C1:C5").Offset(Ro, Clm).Address & "<>0,1,0))")
    If K = 0 Then
    MsgBox "The range tallied is " & Range("C1:C5").Offset(Ro, Clm).Address & "."
    GoTo Line1
    End If
    Next Ro
Next Clm

If K <> 0 Then MsgBox "Thereare no tallied ranges."
Line1:

End Sub

 

[c0087]

Macro code

Sub RangeTally()
Dim K As Long, Ro As Long, Clm As Long
For Clm = 0 To 3
    For Ro = 0 To 5
    K = Evaluate("=SUM(IF(A1:A5-" & Range("C1:C5").Offset(Ro, Clm).Address & "<>0,1,0))")
    If K = 0 Then
    MsgBox "The range tallied is " & Range("C1:C5").Offset(Ro, Clm).Address & "."
    GoTo Line1
    End If
    Next Ro
Next Clm

If K <> 0 Then MsgBox "Thereare no tallied ranges."
Line1:

End Sub

Works good on its own, however my trigger is

Range("A8").Value = 1
Loop

I get the "Loop without Do error" when adding it in.
Since the values in A1:A5 are always changing, I need to Loop your code until one of them is successful

 

[kvsrinivasamurthy]

I didn't follow. Explain clearly.

 

[c0087]

I didn't follow. Explain clearly.

for A1:A5, put in =RANDBETWEEN(1,100) so that they change every input like my cells do.
in A8 (or any cell), i just put the number 1 to act as my trigger so that once a macro is first run, the cells in A1:A5 will automatically change until it your code finds a match.
in the past, i have just put in " Range(A8).value=1 Loop " into a code I want to continuously loop, but it did not work in yours unfortunately.

Basically your code itself works, I just need a loop for it, so that once I run it once, it won't stop until those changing numbers in cells A1:A5 match a pattern in C1:F10.
I like the message box that shows the range where the pattern is located, but we won't need the one that says "There are no tallied ranges", since the macro itself won't stop running until there is a match.

 

[kvsrinivasamurthy]

There is a possibility that the macro may resulting in endless loop. So it is desirable to come out of the macro after certain number of trails(say 100000), even if the desired result is not achieved.

 

[c0087]

There is a possibility that the macro may resulting in endless loop. So it is desirable to come out of the macro after certain number of trails(say 100000), even if the desired result is not achieved.

Yes that would be fine too

 

[kvsrinivasamurthy]

Code.I made some attempts. I did not get the tallied range. Counting cell is H1

Sub RangeTally()
Dim K As Long, Ro As Long, Clm As Long

Range("A1:A5").Formula = "=Randbetween(1 , 100)"
[H1] = 0
Do While [H1] < 10001
For Clm = 0 To 3
    For Ro = 0 To 5
    K = Evaluate("=SUM(IF(A1:A5-" & Range("C1:C5").Offset(Ro, Clm).Address & "<>0,1,0))")
    If K = 0 Then
    MsgBox "The range tallied is " & Range("C1:C5").Offset(Ro, Clm).Address & "."
    GoTo Line1
    End If
    Next Ro
Next Clm
[H1] = [H1] + 1
Loop
If K <> 0 Then MsgBox "There is no tallied ranges."
Line1:

End Sub

18​		1​	2​	3​	4​		10001​
73​		5​	6​	7​	8​
82​		9​	10​	11​	12​
76​		13​	14​	15​	16​
26​		17​	18​	19​	20​
		21​	22​	23​	24​
		25​	26​	27​	28​
		29​	30​	31​	32​
		33​	34​	35​	36​
		37​	38​	39​	40​
		41​	42​	43​	44​
		45​	46​	47​	48​
		49​	50​	51​	52​
		53​	54​	55​	56​
		57​	58​	59​	60​
		61​	62​	63​	64​
		65​	66​	67​	68​
		69​	70​	71​	72​
		73​	74​	75​	76​
		77​	78​	79​	80​
		81​	82​	83​	84​
		85​	86​	87​	88​
		89​	90​	91​	92​
		93​	94​	95​	96​
		97​	98​	99​	100​
		101​	102​	103​	104​
		105​	106​	107​	108​
		109​	110​	111​	112​

 

[c0087]

it works for the example, but i'm having trouble editing it for what i actually have.

a1:a5 is j66:j70

j66 =VLOOKUP(I66,$B$2:$D$71,3)
j67 =VLOOKUP(I67,$B$2:$D$71,3)
j68 =VLOOKUP(I68,$B$2:$D$71,3)
j69 =VLOOKUP(I69,$B$2:$D$71,3)
j70 =VLOOKUP(I70,$B$2:$D$71,3)

these cells are vlooking up off a randbetween column so they change similar to my example

data range c1:f10 is v2:aa2825

i need to be able to understand how to edit the range, since it increases/decreases on certain sheets. v:aa will always be the same, just the "2825" will change.

 

[kvsrinivasamurthy]

I edited the code to suit the ranges as you wanted. V:AA automatically it selects the full range. Only V2 should be starting cell.
If required J66:J70 can be changed, as required, in the code.

Sub RangeTally()
Dim K As Long, Ro As Long, Clm As Long, Tros As Long, Tclms As Long
Dim Rng As Range

Set Rng = Range("V2").CurrentRegion
Tros = Rng.Rows.Count - 5
Tclms = Rng.Columns.Count - 1

[H1] = 0
Do While [H1] < 10001
For Clm = 0 To Tclms
    For Ro = 0 To Tros
    K = Evaluate("=SUM(IF(J66:J70-" & Range("V2:V6").Offset(Ro, Clm).Address & "<>0,1,0))")
    If K = 0 Then
    MsgBox "The range tallied is " & Range("V2:V6").Offset(Ro, Clm).Address & "."
    GoTo Line1
    End If
    Next Ro
Next Clm
[H1] = [H1] + 1
Loop
If K <> 0 Then MsgBox "There is no tallied ranges."
Line1:

End Sub

Any more clarifications required, you are welcome.