Creating an advanced Excel macro

Hi,

I have a database holding about 90.000 lines, which I am constructing in Excel and importing into MuSQL frem there.

In this database I have a column that holds three names.

What I need is a macro that grabs the last of those three words and copy it to the cell one column to the right (which is empty)

For example I have this in the two columns:
Sk-rmbillede-2017-12-06-07.04.25.png
And what I need is this:
Sk-rmbillede-2017-12-06-07.05.33.png
My question is, if it is possible to make a macro that does this in selected areas?
Peter KromanSales ExecutiveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Which column contains "Bornholm Vester Knudsker"? You said "I have this in two columns", do you mean the cells are merged?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Paste the following code on a Standard Module like Module1.

Function GetLastWord(ByVal vStr As String) As String
Dim str() As String
str() = Split(VBA.Trim(vStr), " ")
GetLastWord = str(UBound(str))
End Function

Open in new window


And then use this user defined function on the sheet like below...
Assuming your string is in A2, then try
=GetLastWord(A2)

Open in new window

0
Peter KromanSales ExecutiveAuthor Commented:
No merged cells :)
 Bornholm Vester Knudsker is in one cell and the cell to its right is empty.

How do I activate the function? It does not appear as a macro in the macro list ...
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Peter KromanSales ExecutiveAuthor Commented:
I have made a macro like this:
Sub extractLastWord()
GetLastWord (C)
End Sub

When I run this macro I get this error
Sk-rmbillede-2017-12-06-11.51.20.pngsaying "The index is outside the area"
0
Peter KromanSales ExecutiveAuthor Commented:
... and it stops here:

Sk-rmbillede-2017-12-06-11.53.51.png
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's not the way to use the User Defined Function. Maybe you didn't pay attention to what I suggested.

Anyways, try this code and pay attention to the comments in the code and tweak it if required.

Sub GetLastword()
Dim lr As Long, i As Long
Dim x, str() As String

'Assuming the strings are in column C starting from Row2, if not change it as per your requirement
lr = Cells(Rows.Count, "C").End(xlUp).Row
x = Range("C2:C" & lr).Value
For i = 1 To UBound(x, 1)
    str = Split(x(i, 1), " ")
    x(i, 1) = str(UBound(str))
Next i

'Placing the desired output in column D
Range("D2").Resize(UBound(x, 1)).Value = x
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter KromanSales ExecutiveAuthor Commented:
Thanks Subodh,

I believe I have payed attention to what you have suggested, and I also believe that the code you have suggested now is rather different from the first code you sent. I will try it, and work with it, and I will get back to you if needed.

Thanks for your help.
0
Peter KromanSales ExecutiveAuthor Commented:
Tahnks to Subodh.

The macro works beautifully now with this code:
Sub GetLastword()
Dim lr As Long, i As Long
Dim x, str() As String

lr = Cells(Rows.Count, "B").End(xlUp).Row
x = Range("B2:C" & lr).Value
For i = 1 To UBound(x, 1)
    str = Split(x(i, 1), " ")
    x(i, 1) = str(UBound(str))
Next i

Range("C2").Resize(UBound(x, 1)).Value = x
End Sub
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Peter! Glad it worked for you.
The code I suggested earlier was a User Defined Function and it is either used on the sheet as a regular excel formula or inside a sub routine (macro). You tried to use it in a wrong way. That's why I suggested a sub routine (macro) which you can run to get the desired output.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.