Trying to figure out how to use a variable, which contains an address of a value, in a formula.
In the MTBF section i want the formula to divide by Fr which is a variable containing an address. I want the value of that address used in the formula.
Dim Fr As Long Fr = Cells(Rows.Count, "K").End(xlUp).Row + 1 Cells(Fr, "K").Formula = "=SUM(K1:K" & Fr - 1 & ")" 'selects last row of data in 'Failure Rate' column and calculates the sum Dim MTBF As Long MTBF = Cells(Rows.Count, "L").End(xlUp).Row + 1 Cells(MTBF, "L").Formula = "=1/ Fr" 'selects last row of data in 'MTBF' column and calculates 1/sum
Last edited by Dewy; 11-10-2010 at 10:34 AM.
To make the formula work, you can put:
Cells(MTBF, "L").Formula = "=1/" & Fr
But if you want the value in column K that you just calculated, you'd need to put:
Cells(MTBF, "L").Formula = "=1/" & Cells(Fr, "K").Value
Regards
Thank you for the reply, but i was asking more along the lines of obtaining whatever the value of the address would be, not just the value. However solved it with this...
Dim MTBF As Long MTBF = Cells(Rows.Count, "L").End(xlUp).Row + 1 Cells(MTBF, "L").Formula = "=1/L" & Fr & ""
OK, glad you got what you needed.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks