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 KromanSenior Proposal SpecialistAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Peter KromanSenior Proposal SpecialistAuthor 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
 
Peter KromanSenior Proposal SpecialistAuthor 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 KromanSenior Proposal SpecialistAuthor Commented:
... and it stops here:

Sk-rmbillede-2017-12-06-11.53.51.png
0
 
Peter KromanSenior Proposal SpecialistAuthor 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 KromanSenior Proposal SpecialistAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.