Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Select case help (Excel 2000)

    Hi all,
    I'm trying when I select a cell to extract a string from within a string and offset the value to another cell. I have about 8 different strings that I would like to do this , but only one of those strings be would present, in the activecell. I think the select case is the right way to go about it, I'm thinking that maybe I need to set the object here, If I'm right could some one tell me?

    Thanks,
    Darryl.

    Public Sub VC()
    Dim what As String
    Select Case what
    Case "-VC"
    ActiveCell.Replace what:="-VC", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True
    ActiveCell.Offset(0, 1).Value = "-VC"
    Cells.Find(what:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
    Activate
    Case "-JD"
    ActiveCell.Replace what:="-JD", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True
    ActiveCell.Offset(0, 1).Value = "JD"
    Cells.Find(what:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
    Activate
    End select

    End sub

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

    Re: Select case help (Excel 2000)

    In

    Dim what As String
    Select Case what

    the variable what is not assigned a value, so it is an empty string "". Consequently, none of the Case ... parts is ever executed.

    Can you try to explain clearly (in words) what exactly you are trying to do?

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Select case help (Excel 2000)

    Thanks Hans,

    I'm trying to extract a string from within a string in the activecell, then offset the value of that string into the next cell, but I can't figure out how to set a select case up so that if any of my strings are present in the activecell to accoplish that..basically if a cell has NAME-VC-6am then it would extract -VC and offset -VC to the next cell, the NAME-6am would remain.

    Thanks
    Darryl.

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

    Re: Select case help (Excel 2000)

    You need quite different code for that:

    Sub ReplaceSeveral()
    ReplaceOne "-VC"
    ReplaceOne "-JD"
    ' etc.
    End Sub

    Sub ReplaceOne(strWhat As String)
    Dim strValue As String
    strValue = ActiveCell.Value
    If InStr(strValue, strWhat) > 0 Then
    ActiveCell.Value = Replace(strValue, strWhat, "")
    ActiveCell.Offset(0, 1).Value = strWhat
    End If
    End Sub

    Add more lines to ReplaceSeveral as needed, then run this macro. ReplaceOne is an auxiliary procedure, you don't call it by itself.

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Select case help (Excel 2000)

    Thanks Hans

Posting Permissions

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