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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. helmekki

    Calling Macro when using Select Case

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

    Upper Case and Lower Case

    scott, Jan 20, 2005, in forum: Excel Programming
    Replies:
    5
    Views:
    137
    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:
    151
    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:
    124
    Bob Phillips
    Jan 3, 2006
  5. Atreides
    Replies:
    12
    Views:
    279
    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:
    171
    Michael Koerner
    Mar 6, 2008
  7. kevlarmcc

    Case not recognized in Select Case

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