0

I have a worksheet with Column A containing First and Last Name separated by a space. Column L in the same sheet has Preferred First Name but for only some rows. I would like to scroll from row 2 to UsedRange and if a value exists in Column L, then take that value and replace First Name in Column A with that value.

Example: Column A: Thomas Edison Column L: Tom

I would like to change Column A to Tom Edison.

Any help would be highly appreciated.

Community
  • 1
  • 1
Hormuz
  • 37
  • 6
  • This should be first and last of your posting like this. Why? You just post what you want to do. This is not a coding service site. This is an Q & A site. Don't do it again. You should try something what you think by referencing the internet such as tutorials. If you found any problem with you work, you should post your code and say simply about problem. So, we will have to solve together. Visit our [tour](http://stackoverflow.com/tour) site for more information. I am not a master. I just want to understand you. Never give up. Think and Try. You are welcome. – R.Katnaan Aug 07 '15 at 04:45
  • Nicholas, I understand. I have posted code before and asked for suggestions on making it work. I also look up this site for help more than anywhere else. It was just a long frustrating day and I was stuck with this problem after making a ton of progress prior. Agreed on not posting just questions without code. – Hormuz Aug 07 '15 at 16:38

2 Answers2

1
Sub names()
Dim nick As String
Dim last As String
Dim full As String
Dim midd As Integer
Dim lastRow As Long

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For i = 2 To lastRow

    If Cells(i, 12).Value <> "" Then
        nick = Cells(i, 12).Value
        midd = InStr(1, Cells(i, 1), " ")
        last = Mid(Cells(i, 1), midd + 1, 99)
        full = nick & " " & last
        Cells(i, 1).Value = full
    End If

Next

End Sub
findwindow
  • 3,133
  • 1
  • 13
  • 30
  • Thank you very much. This works flawlessly. Once again, my bad on just posting a question with no code. – Hormuz Aug 07 '15 at 16:44
  • 1
    String functions might be faster than split (need to test it). I will upvote it if you replace `midd = WorksheetFunction.Find(" ", Cells(i, 1), 1)` with `midd = InStr(1, Cells(i, 1), " ")` – paul bica Aug 07 '15 at 17:01
  • Wait, really? Split seems more elegant though (less lines) – findwindow Aug 07 '15 at 18:06
  • the code for split is more concise, but for a large number of rows (>100Ks) it may not be faster; I'll compare them eventually – paul bica Aug 07 '15 at 19:28
  • How do you compare? Run a huge loop for both and set a timer? – findwindow Aug 07 '15 at 19:35
  • 1
    something like that, but I try to keep the tests under a minute, gradually increasing the number of rows. I use the Timer and a double to hold the start time and compare at the end, like in [this test](http://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less/30959316#30959316) – paul bica Aug 08 '15 at 00:19
1
Option Explicit

Public Sub updateNames()
    Dim ws As Worksheet, n As Range, p As String, ur As Range
    Set ws = Sheet1
    Set ur = ws.Range("A2:A" & ws.Cells(ws.UsedRange.Rows.Count + 1, 1).End(xlUp).Row)
    For Each n In ur
        p = n.Offset(0, 11).Value2
        If Len(p) > 0 Then n.Value2 = p & " " & Split(n.Value2)(1)
    Next
End Sub
paul bica
  • 10,557
  • 4
  • 23
  • 42