Programming in Excel Lesson 4 - Formulas

Please login in order to report media.

  • Uploaded 8 months ago in the category How to programming

    https://vbatutorialcode.com/formulas/
    Learn how to program in excel with formulas in VBA.

    Use autofill to place formulas alongside data.

    The below code fills down column E to the last row in column A (or 1).
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    Range("E1").Select
    ActiveCell.FormulaR1C1 =_
    "=trim(SUBSTITUTE(RC[-4],CHAR(160),char(32)))"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E" & erow),_
    Type:=xlFillDefault

    The below code fills down column E to the last row in column D (or 4).
    erow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row

    Range("E1").Select
    ActiveCell.FormulaR1C1 =_
    "=trim(SUBSTITUTE(RC[-4],CHAR(160),char(32)))"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E" & erow),_
    Type:=xlFillDefault

  • programmingexcellesson4formulas