1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alteration in Macro

Discussion in 'VBA Macros' started by Rafay Ahmed, Nov 14, 2017.

  1. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    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

    Code (vb):
    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

    Attached Files:

    Last edited by a moderator: Nov 14, 2017
  2. Derek McGill

    Derek McGill Active Member

    Messages:
    135
    What are you trying to do ?
  3. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    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
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,225
    Hi !

    An easy way just respecting Excel object model :​
    Code (vb):
    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 !
    Chirag R Raval and Syedali like this.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    3,225
    A similar way :​
    Code (vb):
    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 !
    Chirag R Raval and Derek McGill like this.
  6. Derek McGill

    Derek McGill Active Member

    Messages:
    135
    Try this cleaned up code
    Code (vb):

    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
     
    Chirag R Raval likes this.

Share This Page