functions or vba solution

[rumkus]

Nothing came out from Functions maybe here?

Sheet1 - logged ticket books to sales persons

name beginno endno
a 51 100
b 101 150


I do below checks by vba if user makes a wrong entry say


d 75 - msgbox " Number already in use "
e 151 141 msgbox " Wrong entry"


But couldn't do below check


f 1 200 msgbox " Numbers already in
use"


Any help ? Thank you

 

[merjet]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range
Dim iCt as Long
If Target.Column = 2 And Target <> "" Then
Set rng = Range("B2:B" & Target.Row - 1)
For Each c In rng
If Target >= c And Target <= c.Offset(0, 1) Then
MsgBox "Error. Number already in use."
Target = ""
End If
Next c
End If
If Target.Column = 3 And Target <> "" Then
If Target < Target.Offset(0, -1) Then
MsgBox "Error. End number must be >= begin number."
Target = ""
Exit Sub
End If
Set rng = Range("B2:B" & Target.Row - 1)
For iCt = Target.Offset(0, -1) + 1 To Target
For Each c In rng
If Target >= c And Target <= c.Offset(0, 1) Then
MsgBox "Error. Number(s) already in use."
Target = ""
End If
Next c
Next iCt
End If
End Sub

Hth,
Merjet

 

[rumkus]

Thank you very much Merjet.
One more thing though:
How can I avoid flying over numbers ?

Like 1 to 200 ?

 

[merjet]

How can I avoid flying over numbers ?

Like 1 to 200 ?

Oops.
Change: If Target >= c And Target <= c.Offset(0, 1)
To: If iCt >= c And iCt <= c.Offset(0, 1)

Merjet

 

[rumkus]

Exactly what I needed ! Thank you vey much.
Kind regards