Simultaneous nummber

[haseft]

Hi!

Value of range:
A1=1
A2=2
A3=4
A4=5
A5=7
A6=6
A7=3

I want to know if value of range A1:A7 is simultaneous? I mean if the value is 1,2,3,4,5,6,7.
In case above, cell A3 should be 3, and the macro must give a msg box "The cells are not in simultaneous".

Tanks for help.

 

[MickG]

There's probably some pitfalls here , but on the basis that you are testing for consecutive numbers, so each number has the same count between each other number then with you numbers starting in "A1" try this:-

[COLOR="Navy"]Sub[/COLOR] MG08Apr04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Num = Dn.Value - Dn.Offset(-1)
        [COLOR="Navy"]If[/COLOR] InStr(nstr, Num) = 0 [COLOR="Navy"]Then[/COLOR]
            nstr = nstr & IIf(nstr = "", Num, "," & Num)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] UBound(Split(nstr, ",")) = 0 [COLOR="Navy"]Then[/COLOR]
    MsgBox "Numbers are consecutive"
[COLOR="Navy"]Else[/COLOR]
    MsgBox "Numbers are not consecutive"
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[haseft]

hi Mi

 

[haseft]

hi!

I get error with code
Num = Dn.Value - Dn.Offset(-1)
error "Incompatible"

 

[Rick Rothstein]

I get error with code
Num = Dn.Value - Dn.Offset(-1)
error "Incompatible"

Mick's code works fine for me. Does your vertical range of values start in cell A1?

I have never seen the error "Incompatible" in Excel before. Are you actually using Excel or some other Excel-type competitor product?

I was going to offer you this alternative macro, but if Mick's code does not work for you, I am not so sure this code will work either...

Sub ConsecutiveNumbers()
  Dim Diff As Long
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Diff = Evaluate("MEDIAN(" & .Address & "-" & .Offset(-1).Address & ")")
    If Evaluate("SUM(--((" & .Address & "-" & .Offset(-1).Address & ")<>" & Diff & "))") Then
      MsgBox "Numbers are not consecutive"
    Else
      MsgBox "Numbers are consecutive"
    End If
  End With
End Sub

 

[haseft]

I am really sorry Rick.
I test in range A2.
It working perfect.
Thanks to both.

 

[Rick Rothstein]

I am really sorry Rick.
I test in range A2.

The reason Mick and I started in cell A1 is because that is where you started at in Message #1 .