VBA: Loop thru column & replace null values

[PB7]

Hello VBA team. I think this do-able, I'm just missing a nuance I think.

I have a column of data (invoice numbers), which is interspersed randomly with empty or null values. The empty cells will ultimately will get invoice numbers after research.

I would like to loop through the column, simply replacing any empty or null value with "TempInvoiceNo1", "TempInvoiceNo2", and so on.

I thought the code below would work, but no cigar:

Sub PasteTemporaryInv()

Last = Cells(Row.Count, "G").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "G").Value) Is Null Then
Cells(i, "G").Value = "TempInvoiceNo" & "i"
End If
Next i

End Sub


Conceptually, I think I need to find the last row in the column with a value and then work upwards to the top of the column.

When I try this code, I get VBA error code '424' and "object required". Honestly, I don't know what object VBA is looking for. Maybe I am missing some dimensions?

Thanks in advance for any help here, or a better way to do this. Would prefer a looping solution, but will take anything I can get.

 

[Tom Urtis]

Sub test()
Dim rng As Range, xCount&
Set rng = Range("G1:G" & Cells(Rows.Count, 7).End(xlUp).Row)
If WorksheetFunction.CountBlank(rng) > 0 Then
Application.ScreenUpdating = False
xCount = 1
Dim cell As Range
For Each cell In rng.SpecialCells(4)
cell.Value = "TempInvoiceNo" & xCount
xCount = xCount + 1
Next cell
Application.ScreenUpdating = True
End If
Set rng = Nothing
End Sub

 

[mikerickson]

Perhaps something like this non-looping code.

Sub test()
    Dim Prefix As String, formulaString As String
    
    Prefix = "TempInvoiceNo"
    formulaString = "=" & Chr(34) & Prefix & Chr(34) & "&(COUNTIF(R1C:R[-1]C," & Chr(34) & Prefix & "*" & Chr(34) & ")+1)"
    
    On Error Resume Next
    With Sheet1.Range("A:A")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeBlanks)
            .FormulaR1C1 = formulaString
        End With
        .Value = .Value
    End With
    
    On Error GoTo 0
End Sub

 

[PB7]

Tom, Mike, wow! great, quick replies there! I just got back home from the office...let me test out both solutions now and confirm back here asap. Many thanks.

 

[PB7]

Tom, your code seems to cycle through the column, however only the first 3 rows were correctly upated as desired. Other sample empty cells that I positioned beneath the 1st 3 rows, were not updated. Is there something in the code that I need to tweek to denote this column having 1,500+ rows? Or is there a cell type/format issue? I am encouraged in that I get no VBA error message. The screen flickers for a second, due to the updating being turned off. Any ideas on how to update all the blank cells? Many thanks. Fellow Michigander says Go State!

 

[Tom Urtis]

Go State, right on. Graduated from there in 1982 and been in Cal since.

I suspect that your cells that *look* blank really are not empty, maybe they have returned null string values from a formula, or they have a stray space bar character.

Take a close look at cells you think are blank, test them with the len function, and see how many characters are in them. If even one character is in a cell, it is not regarded as empty.

Also verify that it is column G you are dealing with...look at the column header to make sure. It might be that, depending on what the cell actualy contains -- not what it *looks like* it contains but actually contains -- will determine the proper method to fill them all in with what you want.

 

[PB7]

Tom,

You were right on...when I did Clear All to some sample cells, the code plunked in the replacement text nicely to those cells. I'm all set, many thanks.

Mike,

I tried your code a couple times, and it worked, and I must have had the same issues Tom mentioned...cells looked blank, but weren't.

Thanks guys for taking the time here, really appreciated!