Strings & Such...

[Derek_35]

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 ]

[Jay Petrulis]

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.

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 ]

[Derek_35]

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

[Jay Petrulis]

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 ]

[Tom Schreiner]

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 ]

[Derek_35]

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

Using the Val function worked