Microsoft Access VBA Coding to Excel (early binding) 1

[Knicks]

Microsoft Access 2016

I am trying to create code in MS Access VBA to edit a created Excel Spreadsheet (coming from Access). I want to put a formula in Column N that is basically Column L + Column M from row 2 to until Used Rows. Any help on the Range portion would be appreciated.

So in the formula bar for column N2 would look like:
N2 =Sum(L2,M2)
N3 =Sum(L3,M3)
ect.. until I reach the end of my used rows.


Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Worksheet
Dim XLrange As Range
Set xlApp = New Excel.Application
Dim Z As Long


Set WB = xlApp.Workbooks.Open("C:\Database\Excel_Output\WK41_FCST_COUNTRY.xlsx") 'file reference location
WB.Worksheets("CASA WK41 TOPS DOWN FCST").Cells.Select
Set WS = WB.Worksheets("CASA WK41 TOPS DOWN FCST")

xlApp.Selection.Columns.AutoFit 'make the columns expand to largest selection


Z = WS.UsedRange.rows(WS.UsedRange.rows.Count).row
'lets attempt to loop through the used range to create a sum on each row

For i = 2 To Z
'this works just to put value. does not do formula
'Range("N" & i).Value = Range("L" & i).Value + Range("M" & i).Value



Range("N" & i).Formula = "=Sum(range("L" & i),range("M" & i))"


Next i

 

[combo]

Yoy need to build proper formula string and pass it to Formula property:

[tt]Range("N" & i).Formula = "=L" & i &"+M" & i[/tt]
or
[tt]Range("N" & i).Formula = "=Sum("L" & i & ",M" & i & ")"[/tt]
or
[tt]Range("N" & i).Formula = "=Sum("L" & i & ":M" & i & ")"[/tt]



combo

 

[Knicks]

Thanx,
Works like a charm