• Select Case (Excel 97)

    Author
    Topic
    #379951

    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…

    Viewing 1 reply thread
    Author
    Replies
    • #634448

      Keep looking into those books! Hope this will help! Post back if there is something hard to understand. –Sam

      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
      • #634455

        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!

    • #634449

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

      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

      greetings, pieter.

      • #634462

        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!

        • #634472

          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.

        • #634702

          As Pieter said, there is very little difference in our routines. What specifically did you think was different? –Sam

          • #634727

            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…

            • #634737

              > 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

                  with ActiveSheet
                  for each c in Intersect(.UsedRange,.Columns("J"))

              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

    Viewing 1 reply thread
    Reply To: Select Case (Excel 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: