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
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
Thank you very much :wink:
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 SubCan 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 IThank you very much :wink:
