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

 

[Guest]

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

 

[Nick TKA]

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

 

[paul.robinson]

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

 

[Guest]

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.

 

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

 

[Guest]

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

 

[Ivan Raiminius]

Hi Tom,

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

Regards,
Ivan

 

[Guest]

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.

 

[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

 

[Guest]

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