Alteration in Macro

[Rafay Ahmed]

Hi All,

I want to alter this Macro as for the Sum Total I have to select the ( LAST ROW + 1 ). Also attached is the sheet, for any inconvenience.
Thanks

Sub FMLdailyREPORT()

  Range("D2").Select

  ActiveCell.FormulaR1C1 = "$"

  Range("E2").Select

  Range(Selection, Selection.End(xlToRight)).Select

  Range(Selection, Selection.End(xlDown)).Select

  Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _

  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

  ReplaceFormat:=False

  Selection.End(xlDown).Select

  Range("E58").Select

  ActiveCell.FormulaR1C1 = "=SUM(R[-56]C:R[-1]C)"

  Range("E58").Select

  Selection.AutoFill Destination:=Range("E58:L58"), Type:=xlFillDefault

  Range("E58:L58").Select

  Sheets("Input").Select

  Range("E58").Select

End Sub

 

[Derek McGill]

What are you trying to do ?

 

[Rafay Ahmed]

What are you trying to do ?

Frequently I change the Input .Tab, I am having issue when I am calculating totals, the row is fixed to Row 58, I want the macro to select the last row.
Moreover, its a RECORDED MACRO, can this be modified.
Thanks

 

[Marc L]

Hi !

An easy way just respecting Excel object model :​

Sub Demo1()
    With [Input!A1].CurrentRegion
        With .Cells(.Rows.Count - (.Cells(.Rows.Count, 1).Value > ""), 5).Resize(, .Columns.Count - 4)
             .NumberFormat = "#,##0.00"
             .Formula = "=SUM(E2:E" & .Row - 1 & ")"
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !

Think, But Think Object !
​

 

[Marc L]

A similar way :​

Sub Demo2()
    With Sheet2.Cells(Rows.Count, 1).End(xlUp)
        With Range(.Cells(2, 5), .Cells(1, 4).End(xlToRight)(2))
            .NumberFormat = "#,##0.00"
            .Formula = "=SUM(E2:E" & .Row - 1 & ")"
        End With
    End With
End Sub

You may Like it !
​

 

[Derek McGill]

Try this cleaned up code

Sub FMLdailyREPORT()

    Dim sht As Worksheet
    Dim LastRow As Long
    Set sht = ActiveSheet
    Dim lngLastColumn As Long
    'Define variables
   
    Sheets("Input").Select
    'Go to correct sheet
   
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "$"
    Range("E2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="$", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.End(xlDown).Select
    'Your code to remove $s ?
   
    LastRow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row
    'Find last Row
   
     With Range("E" & LastRow + 1).End(xlUp)
        .Offset(1).Formula = "=SUM(E2:" & .Address(0, 0) & ")"
    End With
    'Insert Sum Formula
   
    Range("E65536").End(xlUp).Select
    'Move to autofill Row
   
    lngLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Range(ActiveCell, Cells(ActiveCell.Row, lngLastColumn)).FillRight
    'Fill across to last Column
   
End Sub