New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Apply a formula to values in a column

Macros, VBA, Excel Automation, etc.

Re: Apply a formula to values in a column

Postby NoSparks » Tue Sep 13, 2016 8:39 am

If you put the formula as shown into the adjacent cell,
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.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Apply a formula to values in a column

Postby NoSparks » Wed Sep 14, 2016 8:42 am

A formula won't apply formatting to a cell.
Meaning the next thing typed into the cell won't automatically be displayed the way the previous item was and still have the cell retain what was typed in.

Personally I would probably use a formula in the adjacent cell and hide the original column, that way there would still be some control over the original data.

Anyway, this macro will take what's in the cell, manipulate it, and overwrite the cell with the result. What was originally in the cell will no longer exist.
Code: Select all
Sub ChangeTheValues()
' declare all variables
Dim rng As Range    ' this will refer to the range being changed
Dim cel As Range    ' refers to the individual ranges withing rng
Dim lr As Long      ' last row to use
Dim dys As Integer  ' variable for the days
Dim hrs As Integer  ' variable for hours
Dim mns As Integer  ' variable for minutes
Dim str As String   ' variable to hold the combined results

With Sheets("miss_assurance")   'work with this sheet
    lr = .Cells(Rows.Count, "E").End(xlUp).Row  'last used row in the column
    Set rng = .Range("E2:E" & lr)   'the range to work on
    For Each cel In rng     'for each of the cells in the rng
        ' calculate the days
        dys = Int(cel.Value / 24)
        ' the hours
        hrs = Hour(cel.Value / 24)
        ' the minutes
        mns = Minute(cel.Value / 24)
        ' build the desired result
        If dys < 1 Then  'if zero days, ignore it
            str = ""
        Else
            str = dys & " days "
        End If
        'the hours and minutes stay as is
        str = str & hrs & " hrs " & mns & " mins"
        'write this to the cell being worked with
        cel.Value = str
    Next cel
End With
End Sub


Hope that helps.
  • 2

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Apply a formula to values in a column

Postby NoSparks » Thu Sep 15, 2016 9:04 am

Glad I could assist, thanks for the rep points.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 8 guests