using 'Offset' but only counting currently visible cells?

[kbishop94]

I am trying to have my code locate a specific number of cells above the source (starting) cell.

Where this (the code below) does successfully pinpoint the target cell (I confirmed this by changing the cell's content to "P" and then having a MsgBox verify that it indeed correctly finds it):

For Each Cell In Range(Cells(12, 14), Cells(rCol, lCol)).SpecialCells(xlCellTypeVisible)
    If Cell.value = "" And Cell.Offset(-16, 0).value = "P" Then
        MsgBox "Success!"

Ultimately, however, the problem with using the "(-16, 0)" as an offset will not work. What I instead need it to find (instead of -16), is what is in the cell that is 3 VISIBLE cells up from the starting cell.

However, I cant figure out the right combination to do that when using ".SpecialCells(xlCellTypeVisible)":

I have tried:

If Cell.value = "" And .SpecialCells(xlCellTypeVisible).Cell.Offset(-3, 0).value = "P" Then

If Cell.value = "" And Cell.SpecialCells(xlCellTypeVisible).Offset(-3, 0).value = "P" Then

and a couple other number of ways as well but they all give me an error.

Here is why I am needing the code to capture the cell 3 visible cells UP from the target cell:


The target cell in my example provided above happens to be in row 24, but, the specific row number will vary depending on which selection from a dropdown box was previously chosen, BUT, no matter which one is selected, it will always be 3 cells DOWN from the cell that I need it to compare to ("AR:24" is the starting cell, and 3 visible cells up from it is what I need to capture.) (again, I was using the character "P" only to serve as a test in order to confirm I was able to have the code locate it using the offset command)

Thanks for any help!

 

[Alex Blakenburg]

I am sure someone will come up with a more efficient way of doing it but here is one option.

Sub FindVisCell4thLast()

    Dim rngVis As Range, cellTarget As Range
    Dim celVis As Range
    Dim strAdd As String
    Dim arrVis As Variant
   
    Set cellTarget = Range("AR" & Rows.Count).End(xlUp)
    Set rngVis = Range(Cells(1, "AR"), cellTarget).SpecialCells _
       (xlCellTypeVisible)
      
    For Each celVis In rngVis
        strAdd = strAdd & "," & celVis.Address
    Next celVis
   
    strAdd = Right(strAdd, Len(strAdd) - 1)
   
    arrVis = Split(strAdd, ",")
   ' Change the 3 to what ever the offset is you want
    MsgBox "Cell: " & Range(arrVis(UBound(arrVis) - 3)).Address & vbCrLf _
                & "Cell Content: " & Range(arrVis(UBound(arrVis) - 3)).Value

End Sub