Cell Address of First Negative Number in a Column

[ericbartha]

Hello, I am trying to get the address of the first occurance of a negative number in a single column.

So far I have been able to find the value using the below forumla, but I want to the CELL of the value. TIA!

Please Login or Register  to view this content.

[alansidman]

Assuming your data is in column A, then here is a VBA solution.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[wiliam_s]

Hi
Try...

Please Login or Register  to view this content.

Array Formulas
Press CTRL-SHIFT-Enter ( press the CTRL and SHIFT keys, and while these are pressed down, press the Enter key.

[Glenn Kennedy]

Or...

=ADDRESS(SMALL(IF(D:D<0,ROW(D:D),""),1),1)

entered as an array. However, you might be better to avoid whole column references as this might slow your formula down.

Just noticed the reply immediately above. Same approach taken by me... So here's a normal formula to do the same thing:

=ADDRESS(SMALL(INDEX(((D:D>=0))*10^10+ROW(D:D),0),1),1)

[FlameRetired]

Another way also array entered.

Please Login or Register  to view this content.