asked on

VBA code Excel to Find column then look for null cells

Hi,
I have attached sample spreadsheet - dummy data. Data in spreadsheet will sometimes have the last and first names in the same cell. See column RALast (CO). I will get hundreds of records and want to have a macro that will find the RAFirst column (CP) and look for nulls.

If column RA First (CP) has null value and column CO has Last and First name then cut the First name and move it to column CP. Always RALAST RAFirst separted by space.

If column CP has null value and column CO is also null then there is nothing to do.

Thanks.
Samplefile070913.xlsx

Martin Liss

Your data doesn't match your explanation. It looks like first name is CO, last name is CN and CP is an address so where do you want "RALAST RAFirst", CN?

Do the cells in your real data actually say "NULL"?

Martin Liss

I made some assumptions. Try this macro.

Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String

'If column RA First (CO) has null value and column CN has Last and First name then cut the First name and move it to column CO. Always RALAST RAFirst separted by space.
'If column CO has null value and column CN is also null then there is nothing to do.
With ActiveSheet
    lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    For lngRow = 2 To lngLastRow
        ' Check column CO
        If .Cells(lngRow, 93).Value = "NULL" Then
            ' Check column CN
            If .Cells(lngRow, 92).Value <> "NULL" Then
                strParts = Split(.Cells(lngRow, 92).Value, " ")
                If UBound(strParts) > 0 Then
                    .Cells(lngRow, 93).Value = strParts(1)
                End If
            End If
        End If
    Next

End With

End Sub

Open in new window

rhadash

ASKER

Hi,
Sorry about the column name mix up but your assumption is right on- thank you. This code does works but I have one additional question- Can the first name in the RALAST column be cut from the column?

Currently the code does this:
RALAST RAFIRST
PINKNELL MANNY MANNY

would like to have this:
RALAST RAFIRST
PINKNELL MANNY

ASKER CERTIFIED SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

rhadash

ASKER

Thank you

Martin Liss

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013