Translating Formulas For Sub Use

[indi visual]

These two formulas do the exact same thing.


=RIGHT(A2,LEN(A12)-SEARCH("(",A2)+1)


=MID(A2,1+FIND("(",A2)-1,999)


I'm trying to get either one of them to work this way (for a sub using active cell).


=RIGHT(ActiveCell.Value,LEN(ActiveCell.VAlue)-SEARCH("(",ActiveCell.Value)+1)


=MID(ActiveCell.Value,1+FIND("(",ActiveCell.Value)-1,999)


I realize now that the majority of my questions revolve around this particular issue (with strings). But I know there has to be some easy rule of thumb for converting these to sub use.

 

[Luke M]

How about this?

Sub TrimWord()

Dim MyWord As String

Dim xOutput As String

Dim x As Integer


MyWord = ActiveCell.Value

x = InStr(1, MyWord, "(")

If x > 0 Then

xOutput = Mid(MyWord, x)

End If


End Sub

Note that in VB, the 3rd arguement of Mid is optional. For future use, it's better to define a variable =ActiveCell.Value because then you are only reading from the workbook once (Hui talked about that earlier). It also makes it easier to read your code. For myself, I'll just try typing a function command and then open parenthesis. If a tool-tip appears, I know I'm on the right track. If it doesn't, I'll try using the WorksheetFunction object to see if the function I need is in there.


Not as helpful when cycling through cells, but as a "last resot" you can do something like this:

x = Evaluate("=SUM(A2:B2)+ some parts of a formula - G4")


But that can lead to "unexpected" results.

 

[indi visual]

Thanks to you Hui and Dan I'm on the right track.


Thanks a million.