VBA - Formula to reference another workbook

[KaaaK]

Hey everyone,

I have a Formula code to caluclate the average of a column, ignoring all zeros that works, see below.

Now I want that formula to reference to another closed "Source workbook", not the workbook the makro is in.
I already achieved copying from that "Source workbook" and pasting into my makro workbook
But I want to immediatly apply the formula onto the data in the "Soure workbook" without needing to copy the data.

How can I adress the "Source Workbook" with my Formula?


The Formula Code:

Sub Formula_Active_Row()
Dim r As Long, rr As Long, c As Long
Dim wrng As String

r = 2
rr = Cells(Rows.Count, "D").End(xlUp).Row
wrng = "$D$" & r & ":$D$" & rr
Cells(ActiveCell.Row, 4).Formula = "=AVERAGEIF(" & wrng & ","">0""," & wrng & ")"

End Sub

All help is much appreciated

 

[ThanP]

Hi,

You can try this :

Option Explicit

Dim WB As Workbook
Dim WS As Worksheet
Dim r, rr, c As Long 'Note here, you don't need to re-declare as Long every time
Dim wrng As String

Sub Formula_Active_Row()
Set WB = Workbooks("Book2.xlsx") 'Change WB name here. Assuming the WB is in the same folder. If not, you will have to paste to path
Set WS = WB.Worksheets("Sheet1")  'Change sheet name here. 


r = 2
rr = Cells(Rows.Count, "D").End(xlUp).Row
wrng = "$D$" & r & ":$D$" & rr

WS.Cells(ActiveCell.Row, 4).Formula = "=AVERAGEIF(" & wrng & ","">0""," & wrng & ")"


End Sub

Hope it helps.
Regards,