CODEWITHSUNDEEP
vba
Anthony Dawson
Anthony Dawson

Reputation: 3

Excel formula into VBA,

I trying found out the best way to put Excel formula into VBA,

=TRIM(RIGHT("2,9)&CONCAT(": ",Q2,"-A"))

I have tried this

Sub upstreamA()
    i = Right("P2", 9)
    f = Application.WorksheetFunction.Trim(i, Application.WorksheetFunction.Concat(": ", "Q2", "-A").Value, Range("W23").Value)
End Sub

but no luck..

Upvotes: 0

Views: 55

Answers (2)

Chris Strickland
Chris Strickland

Reputation: 3490

To use custom functions, you will need to save the file as .xlsm or one of the other macro enabled formats.

arg1 and arg2 are the values of the cells, and this should work. Put it wherever you want the value return, and just reference the input cells like you would in any normal function:

=upstreamA(P2, Q2)

Function upstreamA(arg1 As String, arg2 As String) As String
    
    Dim buffer As String, i As String
    buffer = ""

    i = Right(arg1, 9)
    buffer = i & ": " & arg2 & "-A"
    
    upstreamA = Application.WorksheetFunction.Trim(buffer)

End Function

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Your "P2" and "Q2" need to be Ranges, otherwise vba does not know they are references and not strings in their own right.

Also you need to put the range in which you want the values to go on the Left of the =

Lastly, get in the habit of declaring you variables and using Option Explicit on the top of your modules. It will help find typos.

Sub upstreamA()
    Dim i as String
    i = Right$(ActiveSheet.Range("P2"), 9)
    ActiveSheet.Range("W23").Value = Application.WorksheetFunction.Trim(i & ": " & ActiveSheet.Range("Q2") & "-A")
End Sub

Upvotes: 0

Related Questions