moving values between cells?

[termeric]

hello,

i have a spread sheet that came to me filled with first and last names all in one column. i went ahead and spread the names over 3 columns using "text to columns".

what im left with now, is a coulmn for the first name(FN), middle initial(MI), and last name(LN). the only problem i encountered is that some people dont have middle initials, so their LN column is blank, and thier MI column has their last name.

can some one please help me with a script that will check the last name column, and if it is blank, take the value out of the middle initial column, and move it over, leaving a blank cell in the MI column?



thanks,

- christian

 

[texasalynn]

take a look at this:
http://www.mrexcel.com/board2/viewtopic.php?t=110080

 

[TrippyTom]

here's a vba solution to your problem, assuming the range is named "names" and it starts in column A (in a 3 column format).

Public Sub MoveLastnames()
Dim myRow As Integer

With Range("names")
    For myRow = 1 To .Rows.Count + 1
        If Cells(myRow, 3).Value = "" Then
            Cells(myRow, 3).Value = Cells(myRow, 2)
            Cells(myRow, 2).Clear
        End If
    Next myRow
End With

End Sub