Check if a cell contains same digits

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
Something like

Code:
MsgBox Range("A1").Text = String(4, Left(Range("A1").Text, 1))
Hi again,
Sorry to wake this thread up again.

But is there a way to verify with numbers like this:
0123
1234
2345
3456
4567
Etc?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
is there a way to verify with numbers like this:
0123
1234
2345
3456
4567
Etc?
Verify what?
- That they are all 4 digits exactly?
- That they are 4 consecutive ascending digits?
- That every digit of 1234 is 1 more than every digit of the previous number (0123) etc?
- Something else?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
Yes to all the above but nothing else
Really? :unsure:

So this is nothing like the previous question?

This time, instead of looking at the cells individually, we have to look at a number of cells as a group and this example would fail the verification?

Excel Workbook
A
10123
23456
31234
42345
Verify



Further, the maximum number of cells that could fit the verification is 7 and that would be these 7 cells?

Excel Workbook
A
10123
21234
32345
43456
54567
65678
76789
Verify


.. or perhaps you misunderstood what I was asking with my third point?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
Sorry I think my explanation was not clear:
This is a new form to put it; I will always have the code look at cell A1.
Then in that cell we are looking for any of those numbers as listed above. Then throw the msgbox as before. I hope this makes it clearer :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
I will always have the code look at cell A1.
Then in that cell we are looking for any of those numbers as listed above.
So, just like data validation?
Except that "those numbers as listed above" finish with "Etc".
Where does the "Etc" end? What is the full list of valid numbers? Is it just the 7 numbers I listed in my second screen shot in post 14?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
-The full list of the numbers is supposed to be unlimited ; any number with the consecutive number form of "ABCD" is part of the list.
- "Etc" is not part of the list I used it there to show the sequence


So in cell A1, if the number has the form ABCD like 0123, 1234, ... Then we call the msgbox
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
-The full list of the numbers is supposed to be unlimited ; any number with the consecutive number form of "ABCD" is part of the list.
So there has to be 4 digits and they have to be consecutive.
Unlimited list??
I'm clearly not understanding. Could you give me one more example of a 4-digit number with consecutive digits other than those 7 examples in the bottom screen shot of post 14?

Can you also confirm that the cell being looked at is formatted as Text, since 0123 would not normally show that leading zero?
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
Yes the format will be as text.

Oh okay sorry. I think that's all the list you have up there. I didn't think through it well enough at first which made me think there could be infinite possibilities.

Yes there should be four consecutive digits
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
Code:
MsgBox InStr(1, "|0123|1234|2345|3456|4567|5678|6789|", "|" & Range("A1").Text & "|") > 0
Edit: Safer would be
Code:
MsgBox InStr(1, Replace("|0123|1234|2345|3456|4567|5678|6789|", "|", Chr(0)), Chr(0) & Range("A1").Text & Chr(0)) > 0
 
Last edited:

Forum statistics

Threads
1,081,441
Messages
5,358,709
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top