Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help with SELECT CASE! (Excel XP - VBA)

    Hi,
    This is a tricky select case - case! (See the attachment for details!)

    The DATA sheet is a list imported by macro from text file. (The data is theirfore volatile, even possibly on column labels!)
    The OVERTIME sheet is a dump from a SAP system! (This sheet will always stay the same, labels and cells and data!)

    My macro must copy the first 3 columns to various sheet. (I removed some sheets in the attacment!). This is fine. The problem lies with the D - J columns. They must be moved to the appropriate columns in the OVERTIME sheet!

    My 2 questions:
    1. How can I get SELECT CASE to check for text values? (Sothat the column can be moved to the right place!)
    2. The data labels might change position, and slightly in spelling!!! ie. "Public Holiday" might be in column F the next time, and it could be spelt "Public Hol."! How can I search for part/beginning of a string for a match? (I see there is a LIKE operator, but I don't know if I am using it correctly!)

    Please HELP ---- Many thanx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with SELECT CASE! (Excel XP - VBA)

    You cannot use Like in Select Case statements, according to the online help. Instead, you can test for the beginning of a string like this:

    Select Case ColID
    ' Match everything beginning with "Public"
    Case "Public" to "Publid"
    ...
    Case ...

    You can only use this to match the start of a string, not to match any part of a string. You still need If ... Then for that:

    If ColID Like "*1.5" Then
    ...
    ElseIf ColID Like "*2.0" Then
    ...

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with SELECT CASE! (Excel XP - VBA)

    <hr>Case "Public" to "Publid"<hr>
    What is the significance of the "d". Public to Publid? Could this be Public to Publix? How does it know that it must look for anything with Public in the string?
    Could you give me an example on another column! If I want to find occurences of "OT 1.5", or "Fridge"?
    Thanx Hans
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with SELECT CASE! (Excel XP - VBA)

    You can specify a range of values in a Case statement by supplying the start and end values with To between them, for example

    Case 6 To 18

    Case "nuts" To "soup"

    If the values are strings, comparison is alphabetic; think of looking up words in a dictionary. So "nuts" to "soup" would include everything that fits between "nuts" and "soup" in a dictionary, such as "nutshell", "nutter", "opera", "snark" and "soul", but not "cupasoup" or "nutcase" or "soups" or "walnut".

    Case "public" to "publid" includes everything that begins with "public", and in fact "publid" too, but that probably won't occur.

    If you want all strings that contain a word such as "public", you cannot use Select Case, as mentioned in my previous reply.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with SELECT CASE! (Excel XP - VBA)

    <P ID="edit" class=small>(Edited by RudiS on 08-Oct-04 13:03. Forgot to add a file into zip attachment!)</P>Thanks a stack Hans.
    Thought I'd show you the completed "Project"
    If you have further improvements on any code -- by all means - Let me know!
    Thanx 4 your help!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help with SELECT CASE! (Excel XP - VBA)

    Hans,
    You can use Like if you use a Select Case True construction - e.g.:
    <pre> For i = 4 To DataTitles.Columns.Count
    ColID = DataTitles.Columns(i).Value
    Select Case True
    Case ColID Like "Public*"
    DataRegion.Columns(i).Copy Destination:=Sheets("OVERTIME").Range("G17")
    Case ColID Like "*1.5"
    DataRegion.Columns(i).Copy Destination:=Sheets("OVERTIME").Range("D17")
    Case ColID Like "*2.0"
    DataRegion.Columns(i).Copy Destination:=Sheets("OVERTIME").Range("E17")
    End Select
    Next i
    </pre>

    Though I'm not sure it's necessarily worth the bother.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with SELECT CASE! (Excel XP - VBA)

    Thanks. It's good to know, but in such a situation, ElseIf is just as readable, I think.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help with SELECT CASE! (Excel XP - VBA)

    I agree - I have only ever really used that construction to avoid having to loop through a load of checkboxes.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with SELECT CASE! (Excel XP - VBA)

    Sheesh -- If only the help had mensioned that! Seems to be one of those MS Programmers secrets that are neatly hidden away sothat the minor puny computer servants can't elevate themselves to that status!!
    Thanx 4 the unveiling of this gem! So there - <img src=/S/razz.gif border=0 alt=razz width=25 height=17> - you MS programmers!
    Regards,
    Rudi

Posting Permissions

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