Junp start my Select Case Macro

Discussion in 'Excel Programming' started by L. Howard, Apr 23, 2014.

  1. L. Howard

    L. Howard Guest

    Working with cells Range("C4:C9")'
    So: if C4 = "E" then return Sheets("Sheet2").Range("C5")
    if C4 = "G" then return Sheets("Sheet2").Range("D5")

    and so on with S and N.

    Then the same for cell C5 except I will go to Sheet3("C5")for E, G, S, N.

    If you can jump start me proper coding with C4 and C5 I believe I can get the rest of the cells, C6, C7, C8 & C9.

    Where each cell will have a different sheet to return from.

    Thanks,
    Howard


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C4:C9")) Is Nothing Then Exit Sub

    Select Case Target.Value

    Target = "E"
    Target.Offset(, 1) = Sheets("Sheet2").Range("C5")

    Target = "G"
    Target.Offset(, 1) = Sheets("Sheet2").Range("D5")

    Target = "S"
    Target.Offset(, 1) = Sheets("Sheet2").Range("E5")

    Target = "N"
    Target.Offset(, 1) = Sheets("Sheet2").Range("F5")

    Target = ""

    End Select

    End Sub
     
    L. Howard, Apr 23, 2014
    #1
    1. Advertisements

  2. L. Howard

    Claus Busch Guest

    Hi Howard,

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

    > Working with cells Range("C4:C9")'
    > So: if C4 = "E" then return Sheets("Sheet2").Range("C5")
    > if C4 = "G" then return Sheets("Sheet2").Range("D5")
    >
    > and so on with S and N.


    try:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C4:C9")) Is Nothing Or _
    Target.Count > 1 Then Exit Sub

    Dim adr As String

    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
    Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
    End If
    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 23, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    On Wednesday, April 23, 2014 12:28:19 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Tue, 22 Apr 2014 23:48:09 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > Working with cells Range("C4:C9")'

    >
    > > So: if C4 = "E" then return Sheets("Sheet2").Range("C5")

    >
    > > if C4 = "G" then return Sheets("Sheet2").Range("D5")

    >
    > >

    >
    > > and so on with S and N.

    >
    >
    >
    > try:
    >
    >
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Intersect(Target, Range("C4:C9")) Is Nothing Or _
    >
    > Target.Count > 1 Then Exit Sub
    >
    >
    >
    > Dim adr As String
    >
    >
    >
    > 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
    >
    > Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
    >
    > End If
    >
    > End Sub
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --
    >
    > Vista Ultimate / Windows7
    >
    > Office 2007 Ultimate / 2010 Professional


    Thanks Claus, that should get me going.

    Howard
     
    L. Howard, Apr 23, 2014
    #3
  4. L. Howard

    L. Howard Guest


    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Intersect(Target, Range("C4:C9")) Is Nothing Or _
    >
    > Target.Count > 1 Then Exit Sub
    >
    >
    >
    > Dim adr As String
    >
    >
    >
    > 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
    >
    > Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
    >
    > End If
    >
    > End Sub
    > Regards
    >
    > Claus B.
    >
    > --


    Claus, I am stunned!!!

    That "If - Then" statement at the bottom is brilliant! Took me a bit to digest it. I was wondering how in the world you knew I had 6 sheets to draw from for my offset returns.

    There is nothing more to fill out.

    Thanks again.

    Howard
     
    L. Howard, Apr 23, 2014
    #4
  5. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 23 Apr 2014 01:29:46 -0700 (PDT) schrieb L. Howard:

    > That "If - Then" statement at the bottom is brilliant! Took me a bit to digest it. I was wondering how in the world you knew I had 6 sheets to draw from for my offset returns.


    you wrote it in your explanation:

    "Then the same for cell C5 except I will go to Sheet3("C5")for E, G, S,
    N.
    If you can jump start me proper coding with C4 and C5 I believe I can
    get the rest of the cells, C6, C7, C8 & C9.
    Where each cell will have a different sheet to return from."



    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 23, 2014
    #5
  6. L. Howard

    L. Howard Guest

    I see, guess I explained it better than I thought.

    Can you show me how I could do the same for the Range("C13:C18") within the same change event macro.

    This relates to evaluating people and there will be several more ranges going down C column. I hoping if I have an example of two I can do the rest myself, but not getting anywhere in my attempts so far.

    I understand this would have to be adjusted to -11 for row 13

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

    but the rest is giving me fits.

    Howard
     
    L. Howard, Apr 23, 2014
    #6
  7. L. Howard

    Claus Busch Guest

    Hi again,

    Am Wed, 23 Apr 2014 03:58:06 -0700 (PDT) schrieb L. Howard:

    > I understand this would have to be adjusted to -11 for row 13
    >
    > Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)


    then try:

    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

    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
    If Target.Row < 10 Then
    Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
    Else
    Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)
    End If
    End If
    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 23, 2014
    #7
  8. L. Howard

    L. Howard Guest

    On Wednesday, April 23, 2014 4:22:45 AM UTC-7, Claus Busch wrote:
    > Hi again,
    >
    >
    >
    > Am Wed, 23 Apr 2014 03:58:06 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > I understand this would have to be adjusted to -11 for row 13

    >
    > >

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

    >
    >
    >
    > then try:
    >
    >
    >
    > 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
    >
    >
    >
    > 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
    >
    > If Target.Row < 10 Then
    >
    > Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
    >
    > Else
    >
    > Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)
    >
    > End If
    >
    > End If
    >
    > End Sub
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >


    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.

    Howard
     
    L. Howard, Apr 23, 2014
    #8
  9. L. Howard

    Claus Busch Guest

    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.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 23, 2014
    #9
  10. L. Howard

    GS Guest

    <FWIW>
    I prefer to put processing code in a standard module that can be
    'called' from any sheet that may need to use that same code. In this
    case I'd probably do something like...

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C4:C9")) Is Nothing _
    Or Target.Count > 1 Then Exit Sub
    Call XferSheetData(Me, Target, Sheets(Target.Row - 2))
    End Sub


    In a standard module:

    Sub XferSheetData(wksSrc As Worksheet, _
    rngSrc As Range, wksTgt As Worksheet)
    Dim sAddr$

    Select Case UCase(wksSrc.rngSrc.Value)
    Case "E": sAddr = "C5"
    Case "G": sAddr = "D5"
    Case "S": sAddr = "E5"
    Case "N": sAddr = "F5"
    End Select

    If sAddr <> "" Then wksSrc.rngSrc.Offset(0, 1) = wksTgt.Range(sAddr)
    End Sub

    I might not include the actual transfer code (last executable line
    above) in this procedure if I configure it as a function to return the
    ref cell address. (Of course, you do know a simple LOOKUP function
    would get the job done without need for VBA! Right?<g>)

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 23, 2014
    #10
  11. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 23, 2014
    #11
  12. L. Howard

    L. Howard Guest


    >
    > Have not tried it, will do so now.
    >
    >
    >
    > Thanks for the advice.
    >
    >
    >
    > Howard


    Hi Claus,

    I found this to work well.

    If Len(adr) > 0 Then

    Select Case Target.Row
    Case 4 To 9
    Dif = 2
    Case 13 To 18
    Dif = 11
    Case 22 To 27
    Dif = 20
    End Select

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

    Thanks.
    Howard
     
    L. Howard, Apr 23, 2014
    #12
  13. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 23 Apr 2014 14:23:39 -0700 (PDT) schrieb L. Howard:

    > Select Case Target.Row
    > Case 4 To 9


    yes, that solution is more reliable


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 24, 2014
    #13
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. helmekki

    Calling Macro when using Select Case

    helmekki, Oct 29, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    101
    John Green
    Oct 30, 2004
  2. scott

    Upper Case and Lower Case

    scott, Jan 20, 2005, in forum: Excel Programming
    Replies:
    5
    Views:
    114
    scott
    Jan 21, 2005
  3. GoFigure

    Why Error Message "End Select without Select Case"?

    GoFigure, Dec 8, 2005, in forum: Excel Programming
    Replies:
    5
    Views:
    125
    Rowan Drummond
    Dec 9, 2005
  4. Werner Rohrmoser

    Select case / case is, multiple arguments

    Werner Rohrmoser, Jan 3, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    109
    Bob Phillips
    Jan 3, 2006
  5. Atreides
    Replies:
    12
    Views:
    256
    Tushar Mehta
    Nov 17, 2006
  6. Michael Koerner

    Change Upper Case to Proper Case

    Michael Koerner, Mar 6, 2008, in forum: Excel Programming
    Replies:
    5
    Views:
    148
    Michael Koerner
    Mar 6, 2008
  7. kevlarmcc

    Case not recognized in Select Case

    kevlarmcc, Mar 29, 2010, in forum: Excel Programming
    Replies:
    4
    Views:
    156
    Wouter HM
    Mar 29, 2010
  8. Prof Wonmug
    Replies:
    5
    Views:
    129
    Dave Peterson
    May 1, 2010
Loading...