Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Select Case (Excel 97)

    Hi Eveyone,

    I have 3 reference books in front of me & I'm losing it!!!

    I'm pretty new to VBA & have a beginners question.

    In column J I have various codes(K05A, KP60RA, 27, etc.). For 15 of those codes I need to put something in Column K.

    K05A = JA
    KP60RA = JP
    27 = J

    For the other codes I need to have it say "Look Up Manually"

    Here is what I need: I need to know how to cycle through column J, evaluate the data, and write to column K and stop when it reaches a blank in Column J.

    I need to do this in VBA because it is part of a longer Macro that will be used every month to format a report from one of our databases..

    I just can't seem to figure out where to begin and need a push in the right direction...

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Excel 97)

    Keep looking into those books! Hope this will help! Post back if there is something hard to understand. --Sam
    <pre>Option Explicit
    Sub Macro1()
    Dim c As Range
    With ActiveSheet.Columns("J")
    For Each c In Range(.Cells(1), .Cells(1).End(xlDown))
    Select Case c.Value
    Case "K05A"
    c.Offset(0, 1) = "JA"
    Case "KP60RA"
    c.Offset(0, 1) = "JP"
    Case "27"
    c.Offset(0, 1) = "J"
    Case Else
    c.Offset(0, 1) = "Look Up Manually"
    End Select
    Next c
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Select Case (Excel 97)

    Thank you Sam! This works beautifully and explains how I have to do a couple of other things I needed to do as well..

    Thanks!

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Select Case (Excel 97)

    Hi Pieter...

    I know the vlookup function, but this has to be done in VBA!

    I used Sam's code and it worked great, but I have a quick question on yours.

    What is the difference between the code Sam gave me and the code you gave me. There seems to be a difference in the way you find the first blank cell as well as the actual code in the Select Case statement.

    Is this just a difference in syntax preference, or is there something more?

    Thanks!

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case (Excel 97)

    hi Awckie,

    the code to find the last cell is exactly the same, it uses the end property of the range object with the xldown parameter (which is the same as pressing the end key and then cursor arrow down in the interface)
    i also used a string variable to temporarily store the value that needs to be filled in in column K, and then issue only one statement (currentcell.offset(0,1)=correspondingvalue, whereas Sam does this in each case.
    i did it this way as it would also optionally allow the inputbox to ask for a value in case the user had to look up manually, should you want to do so.
    anyway, the differences are minor.

    hth, greetings, pieter.

    ps. sorry for the lacking tabs in the code, this was my first post to the list.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case (Excel 97)

    hi Awckie,

    if you really want to do this in VBA, below you'll find the sub. but you can achieve the same easily with the vlookup function (check excel's online help).<pre>Sub MakeList()
    Dim CurrentCell As Range
    Dim CorrespondingValue As String
    With ActiveSheet
    'loop will stop at the first empty cell in column j
    'if you want to loop to the last cell in j with data in, change
    '.Range("J1").End(xlDown) to
    '.Range("J65536").End(xlup)
    For Each CurrentCell In _
    Range(.Range("J1"), .Range("J1").End(xlDown))
    CorrespondingValue = ""
    Select Case CurrentCell.Text
    Case "K05A"
    CorrespondingValue = "JA"
    Case "KP60RA"
    CorrespondingValue = "JP"
    Case "27"
    CorrespondingValue = "J"
    'add the other cases
    'case "xx"
    'correspondingvalue="y"
    'case ...
    Case Else
    CorrespondingValue = "Look up manually"
    End Select
    'comment the case else and the last corresp... line and
    'decomment the following do-loop if you want
    'the user to fill in an inputbox in case
    'he has to do a manual lookup
    'Do
    ' CorrespondingValue = _
    ' InputBox("enter value for " & CurrentCell)
    'Loop Until CorrespondingValue <> ""
    CurrentCell.Offset(0, 1) = CorrespondingValue
    Next
    End With
    End Sub</pre>

    greetings, pieter.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Excel 97)

    As Pieter said, there is very little difference in our routines. What specifically did you think was different? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Select Case (Excel 97)

    Hi Sam,

    I didn't understand the CorrespondingValue = & the difference in the way you found the last empty cell. I got what Pieter was saying in his second post, so I think I'm all good now!

    Thank you guys for all your help! I always know I'll find what I'm looking for on WOPR...

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Case (Excel 97)

    > last empty cell
    Pieter's code "Range(.Range("J1"), .Range("J1").End(xlDown))" is easier to read, so I'd use it. I got sidetracked because I thought that you would want to process all of column J, so initially i had
    <pre> with ActiveSheet
    for each c in Intersect(.UsedRange,.Columns("J"))</pre>

    but when I reread your post and discovered that you wanted the first blank, I changed it to use the End method but kept the column J. Have a great day! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •