If that data.xls workbook is open, you could use:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

With Workbooks("data.xls").Worksheets("Material")
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

Set myCell = ActiveSheet.Range("A1") 'where the formula goes

myCell.Formula = "=sum(" & myRng.Address(rowabsolute:=False, _
columnabsolute:=False, _
external:=True) & ")"

End Sub

If the data.xls workbook is closed, you can open it and run this kind of
thing--or maybe you could just change your formula so that it uses the entire
column. (You'll have to include the path in your formula, too.)


diepvic wrote:

Hi,
I've got a data excel file which is refreshed everyday. The number of data
rows is varied on each day.
I have a formula " =sum('[Data.xls]Material'!D1:D2000) which links to the
data file. D1 is the first row and D2000 is the last row for 31 May 09, for
example.
Could you please help me how to create a macro to update the formula with
the last row of the data after refreshing the file?

Thanks so much for your attention.

--

Dave Peterson

Wow, thanks so much for your advice, Dave.
Actually, I'm using Office 03, therefore I can not select the entire column
for an Array Formula (whereare with Office 07 it's possible).

Could you please advice me how to create a macro with the same requirement
as above but this time, I would like to find the last column in a row and sum
all the data?

Thanks so much.

The sample you posted was just plain old =sum().

If you don't want to estimate (or overestimate!) the range, you can use the same
technique as the previous post.

Dim LastRow as long
With Workbooks("data.xls").Worksheets("Material")
lastrow = .cells(.rows.count,"D").end(xlup).row
End With

I used column D to determine that last used row. Change it to what you need.

I don't have a guess about what your =sumproduct() formula should look like.
You didn't give much info on that.




diepvic wrote:

Wow, thanks so much for your advice, Dave.
Actually, I'm using Office 03, therefore I can not select the entire column
for an Array Formula (whereare with Office 07 it's possible).

Could you please advice me how to create a macro with the same requirement
as above but this time, I would like to find the last column in a row and sum
all the data?

Thanks so much.

--

Dave Peterson

Thanks Dave for your reply

Could you pls advise me how to sum the data from the first column to the
last column in the same row?. I can find the last column using this :

Dim lastcol as long
lastcol = ActiveSheet.Cells(1, columns.count).End(xlToRight).Column
'First row'

However, lastcol will turn out to be a Number which I dont know how to use
to indicate a column reference.
Suppose that my sum is
Range("A2") = _
"=sum(A1:" & lastcol & "1)" 'from A1 to lastcol in 1st row'
But that code is incorrect. Could you help me correct that?

Thanks a lot.

range("A2").formula = "=sum(1:1)"
is the simplest.

range("a2").formular1c1 = "=sum(r1c1:r1c" & lastcol & ")"

R3C26

would be Row 3, column 26 (Z3 in A1 reference style).


diepvic wrote:

Thanks Dave for your reply

Could you pls advise me how to sum the data from the first column to the
last column in the same row?. I can find the last column using this :

Dim lastcol as long
lastcol = ActiveSheet.Cells(1, columns.count).End(xlToRight).Column
'First row'

However, lastcol will turn out to be a Number which I dont know how to use
to indicate a column reference.
Suppose that my sum is
Range("A2") = _
"=sum(A1:" & lastcol & "1)" 'from A1 to lastcol in 1st row'
But that code is incorrect. Could you help me correct that?

Thanks a lot.

--

Dave Peterson

Thanks so much, Dave

the FormulaR1C1 will result in Absolute Reference. Is there any other way
that gives the Relative Reference?