VBA Code HELP PLEASE!!
 

At the minute I am running this code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("e273:g284")) Is Nothing Then
Set rng = Range("e273:g284")
ElseIf Not Intersect(Target, Range("g273:j284")) Is Nothing Then
Set rng = Range("g273:j284")
ElseIf Not Intersect(Target, Range("j273:l284")) Is Nothing Then
Set rng = Range("j273:l284")
End If
Application.EnableEvents = False
If Not rng Is Nothing Then
If Application.CountIf(rng, Target.Cells(1, 1).Value) 1 Then
MsgBox "This vehicle is booked out at this time"
Target.ClearContents
Target.Cells(1, 1).Select
End If
End If
Application.EnableEvents = True
End Sub

the code is perfect for what i need it to do but the only problem i
have is that the codes roll on from each other... I.E:-

E273:G284 - G273:J284 - J273:L284

first ends in G second starts in G, Second ends on J third starts on J

for some reason this doesnt work, the first code gets the prority and
works but the second works in all the columns except the first one.. in
this case the first code is fine, second actually works from H not G and
third works from K not J

can anyone suggest how i can overcome this problem?

Many thanks, Nick


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

the first one handles from E to G. so if Target is in G, it is always
handled by the first condition - it never gets to the second condition.

Perhaps you want


If Not Intersect(Target, Range("e273:f284")) Is Nothing Then
Set rng = Range("e273:g284")
elseIf Not Intersect(Target, Range("g273:g284")) Is Nothing Then
set rng = Range("e273:j284")
ElseIf Not Intersect(Target, Range("h273:i284")) Is Nothing Then
Set rng = Range("g273:j284")
elseIf Not Intersect(Target, Range("j273:j284")) Is Nothing Then
set rng = Range("g273:L284")
ElseIf Not Intersect(Target, Range("j273:l284")) Is Nothing Then
Set rng = Range("j273:l284")
End if

--
Regards,
Tom Ogilvy



"Nick TKA" wrote:


At the minute I am running this code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("e273:g284")) Is Nothing Then
Set rng = Range("e273:g284")
ElseIf Not Intersect(Target, Range("g273:j284")) Is Nothing Then
Set rng = Range("g273:j284")
ElseIf Not Intersect(Target, Range("j273:l284")) Is Nothing Then
Set rng = Range("j273:l284")
End If
Application.EnableEvents = False
If Not rng Is Nothing Then
If Application.CountIf(rng, Target.Cells(1, 1).Value) 1 Then
MsgBox "This vehicle is booked out at this time"
Target.ClearContents
Target.Cells(1, 1).Select
End If
End If
Application.EnableEvents = True
End Sub

the code is perfect for what i need it to do but the only problem i
have is that the codes roll on from each other... I.E:-

E273:G284 - G273:J284 - J273:L284

first ends in G second starts in G, Second ends on J third starts on J

for some reason this doesnt work, the first code gets the prority and
works but the second works in all the columns except the first one.. in
this case the first code is fine, second actually works from H not G and
third works from K not J

can anyone suggest how i can overcome this problem?

Many thanks, Nick


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

thankyou for your response.

this however is unsuitable as i need to scan the columns E to G and
also need to scan G to J and also J to L they have to be covered eac
time.

I tried using a data validation option also but i am unaware of weathe
A, this would make a difference B, you can look accross colums whe
writting the forumla for data validatio

--
Nick TK
-----------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...fo&userid=3394
View this thread: http://www.excelforum.com/showthread.php?threadid=53717

VBA Code HELP PLEASE!!
 

Hi
I'm not clear what your problem is. The obvious issue is that your
ranges overlap, so the behavior you want will depend on the sequencing
of your if.. then...else statements. Exactly what range do you want to
apply if you click on target?

regards
Paul

VBA Code HELP PLEASE!!
 

Wrote:
Hi
I'm not clear what your problem is. The obvious issue is that your
ranges overlap, so the behavior you want will depend on the sequencing
of your if.. then...else statements. Exactly what range do you want to
apply if you click on target?

regards
Paul

Hi Paul,

what I need to make sure that a vehicle cannot be booked out more than
once.

To break it down, There is one vehicle, this is called 481. When 481 is
booked out from 6.30am to 10.30am i need to make sure that it cannot be
booked out again before 10.30am.

So if you imagine that my code E273:G284 covers - E to G being the
times E=6am F=7am G=8am and rows 273 to 284 are drivers

Start times are 6am 8am 11am 12.30pm so my code displays any
duplications between these times. In theory this works but as above
stated the second and third dont do their job properly because the
first column will not run 2 codes.


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

Hi Nick,

maybe you are looking for something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

'another If construction
If Not Intersect(Target, Range("e273:g284")) Is Nothing Then
Set rng = Range("e273:g284")
end if

If Not Intersect(Target, Range("g273:j284")) Is Nothing Then
if rng is nothing then
Set rng = Range("g273:j284")
else
set rng = union(rng, Range("g273:j284"))
end if
end if

If Not Intersect(Target, Range("j273:l284")) Is Nothing Then
if rng is nothing then
Set rng = Range("j273:l284")
else
set rng = union(rng, Range("j273:l284"))
End If
end if

'rest of your code
Application.EnableEvents = False
If Not rng Is Nothing Then
If Application.CountIf(rng, Target.Cells(1, 1).Value) 1 Then
MsgBox "This vehicle is booked out at this time"
Target.ClearContents
Target.Cells(1, 1).Select
End If
End If
Application.EnableEvents = True
End Sub

But maybe I misunderstood your question.

Regards,
Ivan

VBA Code HELP PLEASE!!
 

Ivan, i'm not even sure how this works but it is absolutely spot on.

I've been trying to solve this for days, i cant thank you enough


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

It does everything that I understood you to ask

If you don't understand it, you weren't able to adequately communicate your
needs, then I guess you are on your own.

--
Regards,
Tom Ogilvy





"Nick TKA" wrote:


thankyou for your response.

this however is unsuitable as i need to scan the columns E to G and i
also need to scan G to J and also J to L they have to be covered each
time.

I tried using a data validation option also but i am unaware of weather
A, this would make a difference B, you can look accross colums when
writting the forumla for data validation


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

Hi Nick,

it tests consecutively if target falls into any of the three ranges. If
it falls into first range it simply sets rng = range1. If it falls into
second and third range you need to test if rng is already set, then you
union rng with range2 (or 3), otherwise set rng=range2 (or 3).

I hope this explanation is clear enough, otherwise please let me know.

Regards,
Ivan

VBA Code HELP PLEASE!!
 

Ivan Raiminius Wrote:
Hi Nick,

it tests consecutively if target falls into any of the three ranges
If
it falls into first range it simply sets rng = range1. If it fall
into
second and third range you need to test if rng is already set, the
you
union rng with range2 (or 3), otherwise set rng=range2 (or 3).

I hope this explanation is clear enough, otherwise please let me know.

Regards,
Ivan

this clears things up, and it all works perfectly.

Thankyou very much for your hel

--
Nick TK
-----------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...fo&userid=3394
View this thread: http://www.excelforum.com/showthread.php?threadid=53717

VBA Code HELP PLEASE!!
 

And I gave you just such a solution and much more efficient, but I guess you
couldn't be bothered to test it.

--
Regards,
Tom Ogilvy


"Nick TKA" wrote:


Ivan Raiminius Wrote:
Hi Nick,

it tests consecutively if target falls into any of the three ranges.
If
it falls into first range it simply sets rng = range1. If it falls
into
second and third range you need to test if rng is already set, then
you
union rng with range2 (or 3), otherwise set rng=range2 (or 3).

I hope this explanation is clear enough, otherwise please let me know.

Regards,
Ivan

this clears things up, and it all works perfectly.

Thankyou very much for your help


--
Nick TKA
------------------------------------------------------------------------
Nick TKA's Profile: http://www.excelforum.com/member.php...o&userid=33942
View this thread: http://www.excelforum.com/showthread...hreadid=537179

VBA Code HELP PLEASE!!
 

Hi Tom,

maybe I am wrong, but conditions you wrote fail if
target.address="$E$273,$I$273".

Regards,
Ivan

VBA Code HELP PLEASE!!
 

Do you mean if the user selected and edited both E273 and I273 at the same
time. I will concede that my solution is not designed to handle that.
However, I suppose we could come up with all kinds of outlandish scenarios,
but I doubt that is a contingency Mr. Nick has even considered or would need
to consider or even knows how to do.



--
Regards,
Tom Ogilvy









"Ivan Raiminius" wrote:

Hi Tom,

maybe I am wrong, but conditions you wrote fail if
target.address="$E$273,$I$273".

Regards,
Ivan

VBA Code HELP PLEASE!!
 

yes, for example.

But let's consider much better example - that the user pastes data into
range "e273:i273", which is really expactable to happen.

But I didn't want to come up with million scenarios to prove that my
solution is better. It simply uses different attitude. Not that
efficient, but more "idiotfest".

Regards,
Ivan

VBA Code HELP PLEASE!!
 

I will certainly concede that in that scenario, of the two solutions offered,
your approach would be the best approach.

--
Regards,
Tom Ogilvy


"Ivan Raiminius" wrote:

yes, for example.

But let's consider much better example - that the user pastes data into
range "e273:i273", which is really expactable to happen.

But I didn't want to come up with million scenarios to prove that my
solution is better. It simply uses different attitude. Not that
efficient, but more "idiotfest".

Regards,
Ivan