Macro to search for blanks in a column and enter a static value for each blank

[mclark13]

Hello,

I typically don't post questions because I can typically find my answer; however, I am having a very difficult time applying what I have found to my situation. I am new to VBA and have been using the record function to learn the basics of VBA Macros. So please accept my apology if this has been covered and I have not found it.

So this is what I am trying to do, I have a column in my data that is for telephone numbers. When I receive the file some of the fields are blank in that column. I need to add to my current macro a part that evaluates the column for blanks and adds a static telephone number in the blanks (up to the last row of data in the file). I have been able to accomplish this with the following:

Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "1112223333"

However, when the file that I receive has a telephone number in all the fields the code breaks at this point. I have tried On Error Resume Next, but that just replaces all the remaining cells in the column with the static 1112223333. Thank you in advance for any assistance.

 

[JoeMo]

Try:

Sub StaticValueForBlanks()
Dim lR As Long
On Error Resume Next
With Range("N2", Cells(Rows.Count, "N").End(xlUp)).SpecialCells(xlCellTypeBlanks)
    .Value = "1112223333"
    .NumberFormat = "#########"
End With
On Error GoTo 0
End Sub

 

[mclark13]

JoeMo, Thank you very much. It worked perfectly. Now I will spend the time trying to understand exactly what it does. Again, I really appreciate your quick response.

 

[JoeMo]

JoeMo, Thank you very much. It worked perfectly. Now I will spend the time trying to understand exactly what it does. Again, I really appreciate your quick response.

You are welcome - thanks for your reply.