Address of cell in a range where value is < 0

acat · Jul 2nd 2019

Somewhere in Range i5:i1000 there might be a cell with value under zero
In cell i3 I'd like a formula showing address of such cell, but if none then word NONE
Better yet, In cell i3 I'd like a formula returning a hyperlink to such cell showing it's address, but if none then word NONE

Struggling with getting address of such cell, with following formulas:

=IF(COUNTIF(I5:I1000,"<0"),CELL("address"),"no deductions")
=CELL("address",INDEX(I5:I1000,MATCH("-",I5:I1000,0),1))
=CELL("address",INDEX(I5:I1000,MATCH(COUNTIF(I5:I1000,"<0"),I5:I1000,0),1))

**alansidman** · Jul 3rd 2019

Here is a VBA solution.

Code

Option Explicit


Sub AddressBlank()
    Dim i As Long
    For i = 5 To 1000
        If Range("I" & i) < 0 Then
            'Range("I3") = Range("I" & i).Address
            ActiveSheet.Hyperlinks.Add anchor:=Range("I3"), Address:=Range("I" & i)
        Else
            Range("I3") = "NONE"
        End If
    Next i
End Sub

Display More

*XOR LX* · Jul 3rd 2019

Hi,

Array formula**:

=IFERROR(CELL("address",INDEX(I5:I1000,MATCH(1,N(I5:I1000<0),0))),"NONE")

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

acat · Jul 3rd 2019

XOR LX - Formula works great, if only there was a way not to have to use Array

AlanSidman - VBA skips the If part and goes straight to Else (tried both - with line following If commented out - as you have it, and un-commented)...

**alansidman** · Jul 3rd 2019

acat
Worked for me. Suggest you upload a sample worksheet for testing. Cannot diagnose your issues without seeing your data in a spreadsheet. Formatting may be the issue.

acat · Jul 3rd 2019

AlanSidman - attached a file, when macro is run it looks like for a brief moment i3 does show cell address where value is < 0 (i22 in this case), but then goes to NONE

**alansidman** · Jul 4th 2019

modified:

Code

Option Explicit


Sub AddressBlank()
    Dim i As Long
    For i = 11 To 25
        If Range("I" & i) < 0 Then
            Range("I3") = Range("I" & i).Address
            'ActiveSheet.Hyperlinks.Add anchor:=Range("I3"), Address:=Range("I" & i)
            Exit Sub
        Else
            Range("I3") = "NONE"
        End If
    Next i
    MsgBox "completed"
End Sub

Display More

*XOR LX* · Jul 4th 2019

Quote from acat

XOR LX - Formula works great, if only there was a way not to have to use Array

If by 'array' you mean with CSE (CTRL+SHIFT+ENTER) then a non-array version is:

=IFERROR(CELL("address",INDEX(I5:I1000,MATCH(1,INDEX(N(I5:I1000<0),0),0))),"NONE")

Regards

acat · Jul 16th 2019

AlanSidman - (sorry, I was out for a week) latest code above does show correct cell address where value is < 0, however with below line un-commented, while it turns the cell address into a hyperlink, upon clicking the hyperlink dialog box pops up "Cannot open specified file", instead of selecting the target cell. With target cell being i22, when I right click into i3 containing the hyperlink and select "Edit Hyperlink" Address field shows "-338076"

Code

ActiveSheet.Hyperlinks.Add anchor:=Range("I3"), Address:=Range("I" & i)

Address of cell in a range where value is < 0

Participate now!

Share