select that cell,
goto the Imediate window in the VBE,
type in ?activecell.formulaR1C1 and hit enter,
what displays is what Excel is seeing in the cell and can be used in VBA.
The catch to using this in a macro is that the quote marks must be doubled up.
With your attached file, taking the formula from above and putting it into I2 and doing the above shows
- Code: Select all
=IF(1 < RC[-4]/24, INT(RC[-4]/24)& " days ", "") & HOUR(RC[-4]/24) & " hrs " & MINUTE(RC[-4]/24) & " mins"
this can be used in your macro like this
- Code: Select all
Sub FillFormula()
Range("I2").FormulaR1C1 = "=IF(1 < RC[-4]/24, INT(RC[-4]/24)& "" days "", """") & HOUR(RC[-4]/24) & "" hrs "" & MINUTE(RC[-4]/24) & "" mins"""
Range("I2", "I" & Cells(Rows.Count, "E").End(xlUp).Row).FillDown
End Sub
Hope that helps.