Pasting last value in row before hitting #N/A

[Julie]

I have the calculation
=IF(B19<=0,NA(),B19/B18) in cell B21and
=IF(C19<=0,NA(),C19/C18) in cell C21 and so on that will return a number
or #N/A in each of the cells in row 21. I want to copy the last value
in row 21 that does not read #N/A and paste it into cell A11.
Thanks,
Julie

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Tumbleweed]

Here's something to start with,
*****
Sub check_NA()

Dim iColumn As Integer
Dim bStatus As Boolean

' set iColumn for start of for-next loop at column B
iColumn = 2

For iColumn = 2 To 10
' check for #N/A IsError returns True if #N/A
bStatus = Application.WorksheetFunction.IsError(Cells(21, iColumn))
If bStatus Then
' Set A1 equal to the value of the cell one back from the first #N/A
Cells(11, 1).Value = Cells(21, iColumn - 1).Value
' When the #N/A is found stop the for-next by setting iColumn
greater than 10
iColumn = 11
End If

Next iColumn


End Sub

*****

 

[Julie]

Thanks so much! It works!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **