Programming in Excel Lesson 4 - Formulas

Опубликовано: 17 Октябрь 2024
на канале: VBA Tutorial

155

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