VBA Code HELP PLEASE!!

[Nick TKA]

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

[Tom Ogilvy]

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
>
>

[Nick TKA]

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

[[email protected]]

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

[Nick TKA]

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.

[Ivan Raiminius]

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

[Nick TKA]

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

[Tom Ogilvy]

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
>
>

[Ivan Raiminius]

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

[Nick TKA]

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

[Tom Ogilvy]

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
>
>

[Ivan Raiminius]

Hi Tom,

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

Regards,
Ivan

[Tom Ogilvy]

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
>
>

[Ivan Raiminius]

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

[Tom Ogilvy]

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
>
>