CODEWITHSUNDEEP
vbaexcelexcel-formula
user8517443
user8517443

Reputation:

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

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)

Upvotes: 1

Views: 1030

Answers (1)

dwirony
dwirony

Reputation: 5450

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")

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.

Upvotes: 1

Related Questions