VBA: How to find the next cell in the same row (excluding hidden cells)

Question

I've used a Vlookup to return values in column I, however only half of the cells in this column returned a value (the other half are blank). I'd like my Macro to enter a space " " in all of the empty Vlookup cells rather than keeping the Vlookup equation in the cell. I want to filer all the values in the column and then change the blank cells to =" "

My ultimate goal is to post a Note on each line based on whether cell I has a value or a blank. If cell I for any given line is blank, the cell in column K for that line will auto-fill "Processed Not Yet Paid", if there is a value in cell I, cell K will auto-fill "Paid"

My problem is that I don't know how to write an equation that avoids the hidden cells (hidden from the filter) and changes only the values of the cells displayed after the filter. My column headers are in row 4 and my code is presented below:

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row

Range("A4:K" & Lastrow).Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$K" & Lastrow).AutoFilter Field:=9, Criteria1:="="
Range("I4").Offset(1, 0) = " "
Selection.AutoFill Destination:=Range("Range("I4").Offset(1, 0):I" & 
Lastrow)

Why don't you just wrap your original VLOOKUP formulas in an IFERROR to get your space that you want? Something like =IFERROR(VLOOKUP(A1,B:C,2,FALSE),"") — dwirony
It is not quite clear either what you do or why you do it, or what is your ultimate goal for that matter. Could you please re-write you question to be more clear? — Gene
The Iferror function does not work for my ultimate goal of auto-filling cells based on whether they are blank or have a value. I've updated my question to include my ultimate goal. Please let me know if there is a better way to go about this. Thanks! — user8517443

dwirony dwirony · Accepted Answer · 2017-10-09T15:06:32

There are several ways you can solve your problem. Firstly, you could just put a formula in column K to check if cells in column I are blank:

=IF(I4<>"","Paid","Processed Not Yet Paid")

Or if you're really looking for a macro solution, just go down cell by cell and check the values:

Sub WhoDidntPay()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 4 To lastrow
    If ActiveSheet.Range("I" & i).Value <> "" And _
       ActiveSheet.Range("G" & i).Value = <> 0 Then
        ActiveSheet.Range("K" & i).Value = "Processed Not Yet Paid"
    Else
        ActiveSheet.Range("K" & i).Value = "Paid"
    End If
Next i
End Sub

Again, I suggest adding an IFERROR to your VLOOKUPs so that you can return a consistent value if you're getting errors, or if they're really returning blanks, can just use "" like in the example above.

VBA: How to find the next cell in the same row (excluding hidden cells)

1 Answers