[AccessD] Export to Excel via VBA, Naming Cells

Jim,

Nice example.  But I'd like to point out one thing, if I may.  I think it is
a better practice to exit a for loop with an Exit For statement instead of
jumping out of the loop.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Friday, March 17, 2006 4:27 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Export to Excel via VBA, Naming Cells


Maybe this will give you some ideas. Starting at the top of the range e5 it
finds the last number in the column and places a sum formula underneath.
Jim Hale

Function test()
Dim rng As Range, i As Integer, strAddressBottom As String, strAddressTop As
String
Set rng = Range("e5")
strAddressTop = rng.Address
For i = 1 To 25
    If rng.Offset(i, 0).Value = "" Then
        strAddressBottom = rng.Offset(i - 1, 0).Cells.Address
        rng.Offset(i, 0).Formula = "=sum(" & strAddressTop & ":" &
strAddressBottom & ")"
        GoTo The_End
    End If
Next
The_End:
Set rng = Nothing
End Function