Find and replace based on condition macro

**contra76** · 10-18-2010, 06:40 PM

Hi all,

Query I would like to build:

I have a list of names in column A.

I have a second list of names in column B.

If a cell in column A is empty, replace that cell with the value from column B (right next to it).

example:

John Bob
Jill Jackie
Mary Matt
(emptycell) Jason
Bill Frank

After macro:
John Bob
Jill Jackie
Mary Matt
Jason Jason
Bill Frank

Please help.
Thank you!

**Trebor76** · 10-18-2010, 06:58 PM

Hi contra76,

Here's one possible solution:

Sub Macro1()

    Dim lngLastRow As Long
    Dim rngCell As Range, _
        rngMyRange As Range
    
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rngMyRange = Range("A2:A" & lngLastRow) 'Assumes the data starts from Row 2. Change to suit.
        
    Application.ScreenUpdating = False
    
    For Each rngCell In rngMyRange
        If Len(rngCell.Value) = 0 Then
            rngCell.Value = rngCell.Offset(0, 1).Value
        End If
    Next rngCell
    
    Application.ScreenUpdating = True
    
End Sub

HTH

Robert

**contra76** · 10-18-2010, 07:13 PM

Thanks Robert!

One problem though.

The last couple of cells in column A are empty. Thus the macro does not select those last few and they are left empty once the macro is done. Advice?

Other than that, this is exactly what I'm looking for.

**JBeaucaire** · 10-18-2010, 07:13 PM

Here's an approach with no loop:

Sub ReplaceBlanks()
Dim RNG As Range
On Error GoTo ErrorExit

Set RNG = Columns("A:A").SpecialCells(xlCellTypeBlanks)
RNG.Value = RNG.Offset(, 1).Value

ErrorExit:
End Sub

**Trebor76** · 10-18-2010, 08:39 PM

Hi contra76,

I set the 'lngLastRow' variable based on the last row in Column A where it seems in this case it should have been Column B. In any case, try the following which finds the last row regardless of what Column it resides in:

Sub Macro1()

    Dim lngLastRow As Long
    Dim rngCell As Range, _
        rngMyRange As Range
            
    'Find the last row by searching backwards through all the rows.
    lngLastRow = ActiveSheet.Cells.Find(What:="*", _
                                        After:=[A1], _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlPrevious).Row
    
    Set rngMyRange = Range("A2:A" & lngLastRow) 'Assumes the data starts from Row 2. Change to suit.
        
    Application.ScreenUpdating = False
    
    For Each rngCell In rngMyRange
        If Len(rngCell.Value) = 0 Then
            rngCell.Value = rngCell.Offset(0, 1).Value
        End If
    Next rngCell
    
    Application.ScreenUpdating = True
    
End Sub

Also, have you tried JBeaucaire's clever solution as for large datasets it is the way to go.

Regards,

Robert

**JBeaucaire** · 10-19-2010, 02:54 AM

Did you see/try the post #3 solution? You posted at the same moment I did, so you may have missed it.

**contra76** · 10-25-2010, 08:59 PM

I actually saw it JB and should've posted results back w regards to that solution.

I tried it but it actually ended up misaligning the copy and paste function. I'm not sure how to explain it but it wasn't copying and pasting correctly. I'm actualy very interested in seeing you're version run. If you have time to think of another method I would love to give it a shot.

Thanks!

**JBeaucaire** · 10-25-2010, 10:58 PM

Based on what you've supplied, I would use the macro as I provided it. If you're results are unexpected you should provide a sample workbook with data showing what you start with, what you get from the macro, and what you wanted instead, all in a workbook.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.