Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code HELP PLEASE!!
Hi Tom,
maybe I am wrong, but conditions you wrote fail if target.address="$E$273,$I$273". Regards, Ivan |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |