Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Strings & Such...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In cell A1 the user enters a value (ie O8, U50, etc)

    Two different tasks need to be performed depending on the first letter of the string (O or U) and another task is dependent on the number following the O or U.

    I cant get this to work...

    Function GetRight(stringToRip)
    stringToRip = Right(stringToRip, Len(stringToRip) - 1)
    GetRight = stringToRip
    End Function

    Function GetLeft(stringToRip)
    stringToRip = Left(stringToRip, (1 - Len(stringToRip)))
    GetLeft = stringToRip
    End Function

    Sub Main()

    Dim uValue As String
    Dim OorU As Integer

    uValue = Cells(1, 1)
    If GetLeft(uValue).Value = "O" Then
    OorU = GetRight(uValue).Value
    ' Performs tasks
    End If

    End Sub

    Any help?

    [ This Message was edited by: Derek_35 on 2002-05-12 18:13 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    In the GetRight pass, the uValue is being given the value of the "U" or "O" and has length 1.

    Also, your (1-len()) syntax is incorrect, because you would essentially be taking the rightmost 0 or negative values.

    The following, without the UDF calls, should do the same as you desire.

    Code:
    Sub Main()
    
    Dim uValue As String
    Dim Leftstring As String
    Dim RightString As Integer
    
    uValue = Cells(1, 1)
    Leftstring = Left(uValue, 1)
    RightString = Right(uValue, Len(uValue) - 1)
    
    End Sub
    Then test IF LeftString = "O" THEN do something ELSE do something with LeftString = "U" END IF.

    HTH,
    Jay

    [ This Message was edited by: Jay Petrulis on 2002-05-12 21:48 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your reply.

    I tried your code and I am getting a type mismatch error on the 'RightString = Right(uValue, Len(uValue) -1)'.

    This is the same error as I have been getting...

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I cannot replicate your error.

    Possibly, try to coerce the value to an integer by doing something like:

    Val(Right(uValue, Len(uValue) - 1))

    or

    CInt(Right(uValue, Len(uValue) - 1))

    Give both of these a try and report the results.

    Bye,
    Jay



    [ This Message was edited by: Jay Petrulis on 2002-05-12 21:47 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay's code worked for me as well.
    Do all of the values entered in Cell A1 end with an integer?
    Also if you have references to other object models which use the Right or Left functions, you may need to qualify yours as a VBA runtime function like this:
    Strings.Left
    or
    Strings.Right
    Probably not the latter in this case, but I have had problems with this before.
    Tom

    [ This Message was edited by: TsTom on 2002-05-12 20:45 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Once again, thanks for the replies. Your help is truly appreciated.

    Using the Val function worked

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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