Easier way?

[Guest]

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.

 

[Jim Rech]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Works great. Just what I was looking for.

Thanks again,
Ernst.