macro to find blank cell nearest to 65

[tonywatsonhelp]

Hi Everyone,
I thought this would be easy but not that i can do.
All i want is to lookdown column AZ and find the nearest cell to row 65 but not less than 50 that fits this crite: below and put the row number in BA1,

There must be two blank cells together, if there are no two blank cells then find nearest with one blank cell, if there are no blank cells default to 65

Please help if you can

Thanks
Tony

 

[jasonb75]

Try this one, array confirmed with Ctrl Shift Enter

=IFERROR(1/(1/MAX(IF(AZ50:AZ64&AZ51:AZ65="",ROW(AZ51:AZ65)))),IFERROR(1/(1/MAX(IF(AZ50:AZ65="",ROW(AZ50:AZ65)))),65))

I've set it so that the higher row of the 2 blanks is shown, i.e. if 58 and 59 are blank then it will show 59. With 2 blanks, the lowest number shown will be 51, not 50. To show 50, row 49 would have to be blank. Row 50 will still be shown as a single blank if it is the only blank in the entire range.

 

[tonywatsonhelp]

Hi, Thanks for your help but it needs to be a macro not a formula, its a long story but trust me its the only option.

 

[Peter_SSs]

See if this does what you want.

Sub FindBlanks()
  Dim rA As Range, RBlanks As Range
  Dim rw1 As Long, rw2 As Long
  
  rw1 = 65
  On Error Resume Next
  Set RBlanks = Range("AZ50:AZ65").SpecialCells(xlBlanks)
  On Error GoTo 0
  If Not RBlanks Is Nothing Then
    For Each rA In RBlanks.Areas
      If rA.Rows.Count = 1 Then
        rw1 = rA.Row
      Else
        rw2 = rA.Row
      End If
    Next rA
  End If
  Range("BA1").Value = IIf(rw2 > 0, rw2, rw1)
End Sub

 

[DanteAmor]

Here another macro for you to consider:

Sub cell_nearest()
  Range("BA1") = Evaluate("=IFERROR(ROW(INDEX(AZ51:AZ65,MAX((AZ50:AZ64="""")*(AZ51:AZ65="""")*ROW(AZ51:AZ65))-50,0)),65)")
End Sub

 

[Peter_SSs]

There must be two blank cells together

To clarify, I took this to mean "two or more blank cells together". If that is not what you want, please clarify.
Also, my code returns the top cell of the two (or more) blanks. Re-reading you may have meant the bottom cell. if so, the change to my code would be

      Else
        rw2 = rA.Row
        rw2 = rA.Cells(rA.Rows.Count).Row
      End If

Here another macro for you to consider:

Hi Dante
I think you may have overlooked this ..

if there are no two blank cells then find nearest with one blank cell

.. so you would need another iferror in there I think? My take on doing it with evaluate would be

Sub cellnearest()
  Range("BA1") = Evaluate("=iferror(aggregate(14,6,row(AZ51:AZ65)/((AZ50:AZ64="""")*(AZ51:AZ65="""")),1),iferror(aggregate(14,6,row(AZ50:AZ65)/(AZ50:AZ65=""""),1),65))")
End Sub

 

[DanteAmor]

if there are no two blank cells then find nearest with one blank cell

I omitted this part, here my updated code:

Sub cell_nearest()
  Range("BA1") = Evaluate("=IFERROR(ROW(INDEX(AZ51:AZ65,MAX((AZ50:AZ64="""")*(AZ51:AZ65="""")*ROW(AZ51:AZ65))-50,0)),IFERROR(ROW(INDEX(AZ50:AZ65,MAX((AZ50:AZ65="""")*ROW(AZ50:AZ65))-49,0)),65))")
End Sub

 

[jasonb75]

Hi, Thanks for your help but it needs to be a macro not a formula, its a long story but trust me its the only option.

Then you could simply evaluate the formula (as both @DanteAmor and @Peter_SSs have suggested above).

Range("BA1".Value = Evaluate("=IFERROR(1/(1/MAX(IF(AZ50:AZ64&AZ51:AZ65="""",ROW(AZ51:AZ65)))),IFERROR(1/(1/MAX(IF(AZ50:AZ65="""",ROW(AZ50:AZ65)))),65))")

 

[tonywatsonhelp]

WOW i tried both and can get the result i need from either.
So a big thank you to Peter, Dante and Jason. this has solved my problem thanks
Tony

 

[Peter_SSs]

Cheers. Glad we could help. Thanks for the confirmation.