On Wednesday, April 23, 2014 9:32:06 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 23 Apr 2014 09:19:22 -0700 (PDT) schrieb L. Howard:



Thanks Claus, I'll see how many evaluation blocks I can add before I crash. I'm thinking I can use statements like < and/or to define more than two evaluation blocks.



you can insert a Select Case into the

If Len(adr) 0 Then statement:



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9, C13:C18")) Is Nothing Or _

Target.Count 1 Then Exit Sub



Dim adr As String

Dim Dif As Long



Select Case UCase(Target.Value)

Case "E"

adr = "C5"

Case "G"

adr = "D5"

Case "S"

adr = "E5"

Case "N"

adr = "F5"

End Select



If Len(adr) 0 Then

Select Case Target.Row

Case Is < 10

Dif = 2

Case Is < 20

Dif = 11

End Select



Target.Offset(, 1) = Sheets(Target.Row - Dif).Range(adr)



End If

End Sub







Regards

Claus B.


Here's what I did, which works.

If Len(adr) 0 Then
If Target.Row < 10 Then
Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)

ElseIf Target.Row 12 And Target.Row < 19 Then
Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)

ElseIf Target.Row 21 And Target.Row < 28 Then
Target.Offset(, 1) = Sheets(Target.Row - 20).Range(adr)
End If
End If


I like the Len(adr) select case.

In your example it seems both cases would be true if the Row was greater than 20. So would I use a and a < to 'capture' the rows?

Have not tried it, will do so now.

Thanks for the advice.

Howard