Not sure if there is any native formula. But yes it is possible to do it in vba...
Edit
I have spent my afternoon on this so I hope you try it...
Place this in a module.
Hope this helps...
Code:
Option Explicit
'<---------------- Usage/Instruction--------------------->
'=FindConsecutiveStrings(Range,Number of Places to match)
'-- Example: In Cell A2 type
'=FindConsecutiveStrings(A1:R1,4)
'-- Range is the cells where the data is
'-- Number of Places to match
' For Example 3 for 123
' Or 4 for 6789
' Or 5 for 12345
'<------------- End of Usage/Instruction------------------>
Function FindConsecutiveStrings(rng As Range, Numb)
Dim cl As Range, StringFromRange As String
'-- Concatenate Values
For Each cl In rng
StringFromRange = StringFromRange & cl.Value
Next cl
FindConsecutiveStrings = ChkConNumb(Trim(StringFromRange), Numb)
End Function
Function ChkConNumb(String1, NoOfPlace)
Dim MyErrorMsg As String, Checkstring As String
Dim Count As Integer, Check As Integer
Dim i As Integer, j As Integer, k As Integer
'-- Error Message
MyErrorMsg = "Please enter a number which is Greater than 1 or " & _
"a number which is Lesser than " & (Len(String1) + 1)
'-- Check if the length of consecutive string is
' shorter than the main string
If NoOfPlace > Len(String1) Then
'MsgBox "Please enter a number which is Lesser than " & Len(String1)
ChkConNumb = MyErrorMsg
Exit Function
End If
'-- Check if the length of consecutive string is
' shorter than 2 Characters
If NoOfPlace < 2 Then
'MsgBox "Please enter a number which is Greater than 1"
ChkConNumb = MyErrorMsg
Exit Function
End If
Count = 0
For i = 1 To Len(String1)
Check = 0
'-- This ensures that it will stop searching on
' reaching the relevant end of the string
If i > Len(String1) - NoOfPlace + 1 Then Exit For
Checkstring = Mid(String1, i, NoOfPlace)
For j = 1 To Len(Checkstring)
If i <> 1 Then
'-- Checks if the starting number is not
' consecutive to the last batch
If Val(Mid(Checkstring, 1, 1)) - _
Val(Mid(String1, i - 1, 1)) <> 1 Then
'-- Checks if the starting number of next batch is not
' consecutive to current batch
If Val(Mid(String1, i + Len(Checkstring), 1)) - _
Val(Mid(Checkstring, Len(Checkstring), 1)) <> 1 Then
For k = 1 To Len(Checkstring) - 1
'-- Final Check if the string is Consecutive
If Val(Mid(Checkstring, k + 1, 1)) - _
Val(Mid(Checkstring, k, 1)) = 1 Then
Check = Check + 1
End If
Next k
If Check = NoOfPlace - 1 Then
Count = Count + 1
'-- Skip searching main string
i = i + NoOfPlace
Exit For
End If
Check = 0
End If
End If
Else
'-- Checks if the starting number of next batch is not
' consecutive to current batch
If Val(Mid(String1, i + Len(Checkstring), 1)) - _
Val(Mid(Checkstring, Len(Checkstring), 1)) <> 1 Then
For k = 1 To Len(Checkstring) - 1
'-- Final Check if the string is Consecutive
If Val(Mid(Checkstring, k + 1, 1)) - _
Val(Mid(Checkstring, k, 1)) = 1 Then
Check = Check + 1
End If
Next k
If Check = NoOfPlace - 1 Then
Count = Count + 1
'-- Skip searching main string
i = i + NoOfPlace
Exit For
End If
Check = 0
End If
End If
Next j
Next i
'-- Store value
ChkConNumb = Count
End Function
Last edited by Siddharth Rout; Jan 31st, 2009 at 07:16 AM.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Hi Paul, I have already given a code which not only accepts a range but also lets you specify "Number of Places to match" See "Usage Instructions" above...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Public Function CountConsec(ListRange As Range, ByVal Seq As Long) As Variant
'-- Accept ListRange of single row or single column only
Dim n As Long, m As Long
If Seq < 1 Then CountConsec = CVErr(2015): Exit Function
If ListRange.Areas.Count > 1 Then CountConsec = CVErr(2042): Exit Function
n = ListRange.Rows.Count
m = ListRange.Columns.Count
If n > 1 And m > 1 Then CountConsec = CVErr(2042): Exit Function
If m > 1 Then n = m
CountConsec = 0
If Seq <= n Then
ReDim ar(1 To n + 1) As Long
For m = 1 To n + 1: ar(m) = 1: Next
For m = 2 To n
If ListRange(m) = ListRange(m - 1) + 1 Then
ar(m) = ar(m - 1) + 1
End If
Next
For m = 1 To n
If ar(m) = Seq And ar(m + 1) = 1 Then
CountConsec = CountConsec + 1
End If
Next
End If
End Function
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
Hi guys. I came back to post the code I've written unti now and I've found all your answers. You're very kind. Thanks to everybody for all the time you've spent to help me. I really appreciate it. Now I'll try all your solutions and I'll post a feedback. Thanks again.
as this thread is from 8 years ago, you should have started a new thread
what is the problem with the calculation?
is it the same problem for all the different codes?
what is the result you are getting? where is it wrong?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
as this thread is from 8 years ago, you should have started a new thread
what is the problem with the calculation?
is it the same problem for all the different codes?
what is the result you are getting? where is it wrong?
Thanks for your answer. I think It would be best to attach an example, but I couldn't attach it here, it gives invalid file type error.
So I share a link : https://1drv.ms/x/s!AoGkZUHlKui9gRPWEiSx5f68ss82
The only one that works is the third code,but the problem with it, as i see, I search for 7 consecutives in the range with 8 consecutives, it doesn't count.
I am sorry about this being an old thread, there are three ways offered here, so I wanted to ask about them.
Last edited by Matten; Aug 27th, 2017 at 09:54 AM.