Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way?
Hello,
In my worksheet_change event I test to make sure that 'Target' is within a certain range but I have many ranges that it could be in. Is there an easier way then using multiple if...then statements? Ex: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("curYear"), Target) Is Nothing Then dOld = Range("curMonth").Value Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value Reload GoTo Done End If If Not Intersect(Range("curMonth"), Target) Is Nothing Then Reload GoTo Done End If If Not Intersect(Range("DayData"), Target) Is Nothing Then GoTo DayData End If If Not Intersect(Range("MonthData"), Target) Is Nothing Then GoTo MonthData End If If Not Intersect(Range("WeekData"), Target) Is Nothing Then GoTo WeekData End If There are more... Might there be an easier way? Ernst. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way?
There is no shortcut to finding the range the active cell is in, so you're
basically going about it the way you have to. -- Jim "Ernst Guckel" wrote in message ... | Hello, | | In my worksheet_change event I test to make sure that 'Target' is within a | certain range but I have many ranges that it could be in. Is there an easier | way then using multiple if...then statements? | | Ex: | | Private Sub Worksheet_Change(ByVal Target As Range) | | If Not Intersect(Range("curYear"), Target) Is Nothing Then | dOld = Range("curMonth").Value | Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value | Reload | GoTo Done | End If | If Not Intersect(Range("curMonth"), Target) Is Nothing Then | Reload | GoTo Done | End If | If Not Intersect(Range("DayData"), Target) Is Nothing Then | GoTo DayData | End If | If Not Intersect(Range("MonthData"), Target) Is Nothing Then | GoTo MonthData | End If | If Not Intersect(Range("WeekData"), Target) Is Nothing Then | GoTo WeekData | End If | | There are more... Might there be an easier way? | | Ernst. | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way?
This might be a little neater? You could replace "isectrng" array with a range which holds all the target ranges you have and loop through this range. Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer, isectrng As Variant isectrng = Array("curYear", "curMonth", "DayData", "MonthData", "WeekData") For i = 0 To UBound(isectrng) If Not Intersect(Range(isectrng(i)), Target) Is Nothing Then Select Case i Case Is = 0 ' curYear ' Code here Case Is = 1 ' curMonth ' Code here Case Is = 2 'DayData ' Code here Case Is = 3 ' MonthData ' Code here Case Is = 4 ' Weekdata ' Code here Case Else 'Anything else End Select End If Next i End Sub "Ernst Guckel" wrote: Hello, In my worksheet_change event I test to make sure that 'Target' is within a certain range but I have many ranges that it could be in. Is there an easier way then using multiple if...then statements? Ex: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("curYear"), Target) Is Nothing Then dOld = Range("curMonth").Value Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value Reload GoTo Done End If If Not Intersect(Range("curMonth"), Target) Is Nothing Then Reload GoTo Done End If If Not Intersect(Range("DayData"), Target) Is Nothing Then GoTo DayData End If If Not Intersect(Range("MonthData"), Target) Is Nothing Then GoTo MonthData End If If Not Intersect(Range("WeekData"), Target) Is Nothing Then GoTo WeekData End If There are more... Might there be an easier way? Ernst. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way?
Assuming your sheet ranges do not overlap then you could improve the code
efficiency using elseif statements. This would speed up the code (only a little bit). As for making the code a little neater what you have is about as neat and tidy as it gets... Generally speaking... Very Nice. HTH "Ernst Guckel" wrote: Hello, In my worksheet_change event I test to make sure that 'Target' is within a certain range but I have many ranges that it could be in. Is there an easier way then using multiple if...then statements? Ex: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("curYear"), Target) Is Nothing Then dOld = Range("curMonth").Value Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value Reload GoTo Done End If If Not Intersect(Range("curMonth"), Target) Is Nothing Then Reload GoTo Done End If If Not Intersect(Range("DayData"), Target) Is Nothing Then GoTo DayData End If If Not Intersect(Range("MonthData"), Target) Is Nothing Then GoTo MonthData End If If Not Intersect(Range("WeekData"), Target) Is Nothing Then GoTo WeekData End If There are more... Might there be an easier way? Ernst. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way?
Works great. Just what I was looking for.
Thanks again, Ernst. "Toppers" wrote: This might be a little neater? You could replace "isectrng" array with a range which holds all the target ranges you have and loop through this range. Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer, isectrng As Variant isectrng = Array("curYear", "curMonth", "DayData", "MonthData", "WeekData") For i = 0 To UBound(isectrng) If Not Intersect(Range(isectrng(i)), Target) Is Nothing Then Select Case i Case Is = 0 ' curYear ' Code here Case Is = 1 ' curMonth ' Code here Case Is = 2 'DayData ' Code here Case Is = 3 ' MonthData ' Code here Case Is = 4 ' Weekdata ' Code here Case Else 'Anything else End Select End If Next i End Sub "Ernst Guckel" wrote: Hello, In my worksheet_change event I test to make sure that 'Target' is within a certain range but I have many ranges that it could be in. Is there an easier way then using multiple if...then statements? Ex: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("curYear"), Target) Is Nothing Then dOld = Range("curMonth").Value Range("curMonth").Value = Month(dOld) & "/1/" & Target.Value Reload GoTo Done End If If Not Intersect(Range("curMonth"), Target) Is Nothing Then Reload GoTo Done End If If Not Intersect(Range("DayData"), Target) Is Nothing Then GoTo DayData End If If Not Intersect(Range("MonthData"), Target) Is Nothing Then GoTo MonthData End If If Not Intersect(Range("WeekData"), Target) Is Nothing Then GoTo WeekData End If There are more... Might there be an easier way? Ernst. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An easier way? | Excel Discussion (Misc queries) | |||
Is there an easier way? | Setting up and Configuration of Excel | |||
got to be an easier way? | Excel Discussion (Misc queries) | |||
There's Got to be an Easier Way | Excel Discussion (Misc queries) | |||
Is there an easier way? | Excel Discussion (Misc queries) |