Check if a cell contains same digits

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,153
Office Version
365
Platform
Windows
@Phouc,

(y) These are both excellent and elegant solutions to the OP's first two questions! Great <del>incite</del> insight into the underlying mathematical structure of the problem!
I agree. (y)


... you can use this somewhat compact code line to do your latest check...

MsgBox [AND(LEFT(A1)=MID(A1,2,1),MID(A1,3,1)=RIGHT(A1),LEFT(A1)<>RIGHT(A1))]
.. or using Phuoc's ideas and your notation

Code:
MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)]
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
Peter_SSs;5331191.. or using Phuoc's ideas and your notation [code said:
MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)][/code]
Good idea. If I am not mistaken, this can be "simplified" (using that word might be a stretch) to this...
Code:
MsgBox [(MOD(LEFT(A1,2),11)+MOD(RIGHT(A1,2),11)=0)*MOD(A1,1111)>0]
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
Interesting!!!

I am really learning more and new tricks each single moments. I really appreciate that:)
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,616
I agree. (y)


.. or using Phuoc's ideas and your notation

Code:
MsgBox [AND(MOD(LEFT(A1,2),11)=0,MOD(RIGHT(A1,2),11)=0,MOD(A1,1111)>0)]
Hi,
I just got into a trouble :


I decided to replace the "A1" with a variable from a user input and I start getting a syntax error alerts.

How do I fix that to take the variable?

Like
.....left(MyInput, 2), 11).......
I thought just taking away the square brackets will do the job yet my thinking was off the grid. :confused:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
I decided to replace the "A1" with a variable from a user input and I start getting a syntax error alerts.

I thought just taking away the square brackets will do the job yet my thinking was off the grid. :confused:
Do you remember when I said "If you are always checking only cell A1 and no other" in Message #29 ... that was because the square brackets require fixed cell references (basically, it is a shortcut notation for evaluating a formula that one might find in a cell). The square brackets are sort of an alternative method for using the Evaluate function. The Evaluate function, however, can use variables because its argument is a text string which means you can concatenate the variable into text string constants (or use VB string functions to embed the variable into the text argument) and pass that into the Evaluate function. So what Peter suggested in Message #31 could be written like this in order to use a variable instead of a cell reference.
Code:
MsgBox Evaluate(Replace("AND(MOD(LEFT(@,2),11)=0,MOD(RIGHT(@,2),11)=0,MOD(@,1111)>0)", "@", YourVariable))
Note 1: I have assumed your variable's name is YourVariable (change it an necessary)

Note 2: I used the @ symbol as a stand-in for your variable in order to avoid multiple concatenations and then used VB's Replace function to change those @ symbols to the value stored in the variable.
 
Last edited:

Forum statistics

Threads
1,081,441
Messages
5,358,694
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