Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions or vba solution
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions or vba solution
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions or vba solution
Thank you very much Merjet.
One more thing though: How can I avoid flying over numbers ? Like 1 to 200 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions or vba solution
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions or vba solution
Exactly what I needed ! Thank you vey much.
Kind regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting MATCH functions? Or other solution? | Excel Worksheet Functions | |||
Looking for a better solution | Excel Worksheet Functions | |||
solution wants | Excel Discussion (Misc queries) | |||
Best Fit Solution | Excel Programming | |||
Is there a solution? | Excel Programming |