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

2
votes

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.

2
vbaexcelexcel-formula
Try changing the double quotes with single quotes in your VBA formula string. – Hakan ERDOGAN
your syntax in vba changes. you have to replace " in your normal function by ""', else vba thinks your string is ending and not that you actually want to write down an ". so try this: reportsheet.Range("A1").Formula = "=TRIM(LEFT(RIGHT("" "" & SUBSTITUTE(TRIM(A20),"" "" ,REPT("" "",60)),180),60))"" – Kajkrow
Thank you guys. However, it still overwrites the contents of the cell instead of applying the formula. Is there something else I am missing? – aso im
the content of which cell? A20 should be unchanged, A1 should show the word you extracted. – FunThomas
I am really sorry, I had a mistake. The code I was testing has A1 in both cases. – aso im

2 Answers

2
votes

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))"
2
votes

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