CODEWITHSUNDEEP
vbaexcelexcel-formula
aso im
aso im

Reputation: 99

How to apply a Excel formula in VBA macro to find the 2nd to last word

I am quite new to VBA but have been working with Excel a bit. I created a formula that does exactly what I need it to do: find the Nth to last word (mostly 2nd or 3rd to last) in a cell. I think my main issue is how to apply a formula to a range of cells without overwriting the cell and how to use excel formulas in VBA. The Excel formula I use is as follows 

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),180),60))

It might not be the most eloquent way but it works pretty well in Excel. Changing the number 180 to 60 will give last word, 120 2nd to last and so on. But in VBA it gives 1st syntax error and when I get it to run without the arguments and with only TRIM(A1) it overwrites the cell. The code I use is as follows (referencing only A1 to test it):

reportsheet.Range("A1").Formula = "=TRIM(LEFT(RIGHT(" " & SUBSTITUTE(TRIM(A1)," " ,REPT(" ",60)),180),60))"

My macro searches and extracts specific data from Sheet1 to Sheet2. Now I would want to apply this (or a similar) formula to the data it extracts to the Sheet2. I have tried a lot of different things from using VBA's own trim to making a completely custom function. None of it seems to work and I think it is down to a misunderstanding on how Excel formulas and VBA play together.

In addition I am trying to find a way to find the only numbers in the cell and trim out everything else. Any help with this would also be appreciated.

EDIT: Sorry guys, I had a mistake in the code I provided, it should have been referring to A1 in both instances.

Upvotes: 2

Views: 315

Answers (2)

FunThomas
FunThomas

Reputation: 29652

Doubling the quotes as @Jeeped and the commenters wrote is solving your issue with the formula. As an alternative, you could write a function ("UDF") that returns the n-th word of a string. It is rather easy by using the VBA function split that returns an array of strings. Put the following code in a Module:

Public Function getWord(s As String, ByVal n As Integer) As String

    n = n - 1   ' Because Array index will start at 0

    Dim arr() As String
    arr = Split(s, " ")
    If UBound(arr) >= n Then
        getWord = arr(n)
    End If

End Function

In Excel, you write for example =getWord(A20, 3) as formula

Upvotes: 2

user4039065
user4039065

Reputation:

Double up quotes within a quoted string or use alternatives.

reportsheet.Range("A1").Formula = "=TRIM(LEFT(RIGHT("" "" & SUBSTITUTE(TRIM(A20),"" "" ,REPT("" "",60)),180),60))"
'alternative
reportsheet.Range("A1").Formula = "=TRIM(LEFT(RIGHT(char(32) & SUBSTITUTE(TRIM(A20), char(32), REPT(char(32),60)),180),60))"

Upvotes: 2

Related Questions