CODEWITHSUNDEEP
excelvba
Condo_programmer
Condo_programmer

Reputation: 236

Translate a formula into VBA

does anyone know how to translate this formula =RIGHT(C1,LEN(C1)-SEARCH(":",C1)) into a VBA syntax? I want to use this formula on my excel C column, each cell contains a ":". I just want to remove the content before ":".

Thanks in advance!

Upvotes: 0

Views: 348

Answers (3)

John Alexiou
John Alexiou

Reputation: 29244

Declare a function in a Module as

Public Function RemoveBeforeColon(ByVal s As String) As String
    Dim i As Integer
    i = InStr(1, s, ":")
    If i > 0 Then
        RemoveBeforeColon = Mid(s, i + 1)
    Else
        RemoveBeforeColon = s
    End If
End Function

and use it with a string or a range

Debug.Print RemoveBeforeColon("ABC:123")

or

Debug.Print RemoveBeforeColon( Sheet1.Range("C1").Value2 )

Upvotes: 1

JoshuaR
JoshuaR

Reputation: 19

You don't say what you want to do with the result

Userdefined Function =mid(c1,find(":",c1))

but

VBA = mid(range("C1"),instr(Range("C1"),":"))

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

An easy way to figure it out is to turn on the macro recorder, then enter your formula. Then you can check the macro for the result. Your issue is mainly that you need to use two quotes back to back in VBA, to reflect one quote in the formula bar.

That being said - to avoid R1C1 style, you'd do Cells(1,1).Formula = "=RIGHT(C1,LEN(C1)-Search("":"",C1))"

Upvotes: 1

Related Questions