Find the First Positive Value

rachael_qiong

New Member
Joined
May 12, 2007
Messages
19
Hi everyone,

Did anyone know how to find the first positive value in a set of value in column AB?

I have tried to find all the positive value in column AB and move it to column AC. But it return nothing.

My scenarios;
1) I have a set of years from 2006 until 2105 (99 years) in column AA (start with AA16)
2) A set of cash flows in column AB corresponding to column AA (start with AB16)

whereas the cash flows are from the formula:
=+SUMIF(A:A, "=" &AA16,Z:Z)

Now I want to find the first cash flow that turn positive in column AB but not succeed yet.

I refered the coding from http://www.mrexcel.com/archive2/49200/57208.htm


Code:
Private Sub OtherCashFlow_Click()
For I = 1 To 3

' Investment in New Business for MTP All (IL)
' --------------------------------------

inputfile_dir = ThisWorkbook.Worksheets("Info-Input").Range("B9").Value
inputfile_name = ThisWorkbook.Worksheets("Info-Input").Range("B10").Value + ".xls"
inputfile = inputfile_dir + inputfile_name

Worksheet_name = ThisWorkbook.Worksheets("Info-Input").Range("B14").Offset(I - 1, 0).Value

'Application.ScreenUpdating = False
    
 Dim starting_year As Range
    
    Workbooks.Open inputfile, UpdateLinks:=0
    ActiveSheet.Range("AA:AB").Select
    Selection.ClearContents
    
    'Column AA is year
    
     ActiveSheet.Range("AA15").Value = ActiveSheet.Range("A15").Value
        
        For s = 1 To 99
            t = Chr(65) & Chr(65) & 15 + s
            u = Chr(65) & Chr(65) & 14 + s
            
        ActiveSheet.Range(t).Formula = "=1+" + u
        
         v = Chr(65) & Chr(66) & 15 + s
        ActiveSheet.Range(v).FormulaR1C1 = "=+SUMIF(C[-27], ""="" &RC[-1],C[-2])"
        
        Next s
        
        ActiveSheet.Columns("AC:AD").Insert
        
    'Loop through needed cells only see if positive
    
    ActiveSheet.Columns("AB").Activate
    n = 16
    Do Until Cells(n, 28) = ""
    If Cells(n, 28) > 0 Then
    Cells(n, 29) = Cells(n, 28)
    End If
    n = n + 1
    Loop
 
Next I

End Sub

Can I know why it return nothing in column AC? It seems like didn't loop until blank cell in column AB and directly go the
Code:
Next I

Thank you very much :wink:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Krishnakumar,

Thanks a lot for your awesome formula. It worked perfectly when i put it in my macro

Code:
ActiveSheet.Range("AC16").Select
Selection.FormulaArray = "=INDEX(RC[-1]:R46C28,MATCH(TRUE,RC[-1]:R46C28>0,0))"

where the formula in excel spreadsheet is

=INDEX(AB16:$AB$46,MATCH(TRUE,AB16:$AB$46>0,0))

and together with Ctrl + Shift + Enter. Can I know why it needs to press Ctrl + Shift + Enter and not Enter only :wink:

Now I hope to find the year in column AB where the first positive value is belong to.

Once I got the year, I have to minus 1 of the year.
Eg: if 2009 returns first positive value that is 196805.6567(which i got in AC16, from krish's formula), then my return year is 2008 (2009 - 1)

I wish to put the return year in cell AD16.

Then I need to add up all the cash flow from 2006(starting year) until 2008(return year) and put in column AE.

Can I know how to do that? Many thanks for the help :)
 
Upvote 0
Now I hope to find the year in column AB where the first positive value is belong to.

Once I got the year, I have to minus 1 of the year.
Eg: if 2009 returns first positive value that is 196805.6567(which i got in AC16, from krish's formula), then my return year is 2008 (2009 - 1)

I wish to put the return year in cell AD16.

Try...

=INDEX($AA$16:$AA$46,MATCH(TRUE,$AB$16:$AB$46>0,0))-1

...confirmed with CONTROL+SHIFT+ENTER

Then I need to add up all the cash flow from 2006(starting year) until 2008(return year) and put in column AE.

Use SUMIF...

=SUMIF(Range,">=2006",RangeToSum)-SUMIF(Range,">2008",RangeToSum)

Hope this helps!
 
Upvote 0
Dear Domenic and Krishnakumar,

Thanks a lot for all the help. I am very appreciated. Thank you! You guys make my day :biggrin:

Domenic, both of your awesome formula worked perfectly!

Krishnakumar, thanks for the link you provided. I will study it.

Thanks once again, guys :biggrin:
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top