Find Replace Loop with last row

[tcnt9176]

I have a loop I am trying to use to find all blank cells in a Column and enter a formula in those blank cells. The loop works but never stops b/c there are blanks in the Column after the data set. I will post the code that I normally use and then what I have tried to fix it without success. Thanks for any help!!!


Started with:

    Do
    If Not Columns("P:P").Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False) Is Nothing Then
        Columns("P:P").Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "=RC[1]"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(0, 1).ClearContents
    Else
        Exit Do
    End If
Loop

I have tried this:

    LastRow = Range("A65536").End(xlUp).Row
 
    Do
    If Not Columns("P:P" & LastRow).Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False) Is Nothing Then
        Columns("P:P & LastRow").Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "=RC[1]"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(0, 1).ClearContents
    Else
        Exit Do
    End If
Loop

And this changing columns to Range:

    LastRow = Range("A65536").End(xlUp).Row
 
    Do
    If Not Range("P:P" & LastRow).Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False) Is Nothing Then
        Range("P:P & LastRow").Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "=RC[1]"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(0, 1).ClearContents     
    Else
        Exit Do
    End If
Loop

 

[hiker95]

tcnt9176,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Change the P1 to the beginning row.

    LastRow = Range("A65536").End(xlUp).Row
 
    Do
    If Not Range([B]"P1:P" & LastRow[/B]).Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False) Is Nothing Then
        Range([B]"P1:P" & LastRow[/B])..Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        ActiveCell.Select
        ActiveCell.FormulaR1C1 = "=RC[1]"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        ActiveCell.Offset(0, 1).ClearContents     
    Else
        Exit Do
    End If
Loop

 

[tcnt9176]

it's giving me an compile error. it says Expected: name parameter and is highlighting "LookAt".

 

[tcnt9176]

sorry. no underscore