copying formulae down a column

[Colin Foster]

Hi,

I have an Excel 2003 spreadsheet which retrieves the majority of its data
from a Query; the spreadsheet then needs to perform a number of calculations
on this data.

If I select the "Fill Down Formulas in columns adjacent to data" property,
then whilst the formulas fill the first time, when the query is refreshed
then the formulas stop working - some have a #REF value & others start
referencing, for example, Row 497 (which is where the previous "version" of
the query's results extended to).

So, two questions...

First of all, am I doing "something wrong" with the query parameters, or is
my error typical?

Secondly, as an alternative, I was going to create a macro to copy the
formulae down to the current last row, but I'm not sure of what code to use,
so any help would be very welcome.

Regards
Colin Foster
ps... Happy New Year!!

 

[Dan R.]

Colin,

Here's an example assuming your formula is in cell B1:

Sub fillToLastRow()
Dim lastRow As Long, fillRange As Range

lastRow = Range("A65536").End(xlUp).Row
Set fillRange = Range(Cells(1, 2), Cells(lastRow, 2))

Range("B1").AutoFill fillRange
End Sub

 

[Charles Chickering]

Colin, while I'm no expert on query's, this does sound normal to me. It sound
like when the query updates it is moving the cells containing the resulting
data. Excel attempts to be intelligent (and fails miserably) by tracking the
cell that it was originally referenced to. While a macro would work, the
"Offset" command might work for you. I learned this trick from Dick Kusleika
at "Daily Dose of Excel"
http://www.dailydoseofexcel.com/

For your formula, instead of using the address of the cells in the query
data, use an "Offset" of the cell it resides in. For example this would be a
formula to sum columns "A" through "G" with the formula residing in column
"H":
=SUM(OFFSET(H1,0,-7):OFFSET(H1,0,-1))
Thus the only "refereneced" cell is the cell that the formula itself resides
in, the data can be moved, deleted etc. and the formula shouldn't break.

If you want to write a macro anyhow, this should get you started:

Sub WriteFormula()
Dim r As Range
Set r = Range("H1:H" & FindLastCell.Row)
r.Formula = "=YourFormula"
End Sub

Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

Let me know if you need any more help.

 

[Colin Foster]

Hi Charles,

Yes... Excel can be SO frustrating when it tries to be helpful!!

I'll try both your Offset method (far easier as it avoids that dreaded
option... Operator Input!) and the macro tomorrow when I'm back in the office
& report back.

Thanks

Colin, while I'm no expert on query's, this does sound normal to me. It sound
like when the query updates it is moving the cells containing the resulting
data. Excel attempts to be intelligent (and fails miserably) by tracking the
cell that it was originally referenced to. While a macro would work, the
"Offset" command might work for you. I learned this trick from Dick Kusleika
at "Daily Dose of Excel"
http://www.dailydoseofexcel.com/

For your formula, instead of using the address of the cells in the query
data, use an "Offset" of the cell it resides in. For example this would be a
formula to sum columns "A" through "G" with the formula residing in column
"H":
=SUM(OFFSET(H1,0,-7):OFFSET(H1,0,-1))
Thus the only "refereneced" cell is the cell that the formula itself resides
in, the data can be moved, deleted etc. and the formula shouldn't break.

If you want to write a macro anyhow, this should get you started:

Sub WriteFormula()
Dim r As Range
Set r = Range("H1:H" & FindLastCell.Row)
r.Formula = "=YourFormula"
End Sub

Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

Let me know if you need any more help.
--
Charles Chickering

"A good example is twice the value of good advice."

Hi,

I have an Excel 2003 spreadsheet which retrieves the majority of its data
from a Query; the spreadsheet then needs to perform a number of calculations
on this data.

If I select the "Fill Down Formulas in columns adjacent to data" property,
then whilst the formulas fill the first time, when the query is refreshed
then the formulas stop working - some have a #REF value & others start
referencing, for example, Row 497 (which is where the previous "version" of
the query's results extended to).

So, two questions...

First of all, am I doing "something wrong" with the query parameters, or is
my error typical?

Secondly, as an alternative, I was going to create a macro to copy the
formulae down to the current last row, but I'm not sure of what code to use,
so any help would be very welcome.

Regards
Colin Foster
ps... Happy New Year!!

 

[Colin Foster]

Dan,

Thanks for this... I'll give it a try tomorrow when I'm back in the office.
Cheers
Colin

 

[Colin Foster]

Dan, Charles,

First of all, further thanks for your advice & assistance. Managed to do
what I needed (I think!! Tested it & it seems to work... now to let the
operator loose!)

In the end, I had to go down the Macro route, rather than using the OFFSET
function as I have a fairly huge formula in one column & I received an error
message stating that the formula was too long!! Basically, it's checking
against a customer's code & depending on the result does a CEILING
calculation... some to th enearest 5p, others 9p.

Anyway, the final macro that I used was as follows...

Sub fillToLastRow()
Dim qrylastRow As Long, fillRange As Range, calclastRow As Long


qrylastRow = Range("A65536").End(xlUp).Row
calclastRow = Range("v65536").End(xlUp).Row

Range(Cells(4, 22), Cells(calclastRow, 29)).ClearContents
Range(Cells(4, 22), Cells(calclastRow, 29)).ClearFormats

Range("v3:Ac3").Copy _
Destination:=Range(Cells(4, 22), Cells(qrylastRow, 22))

End Sub

As you can see, I had to use a copy/paste option, rather than the autofill
as that didn't seem to work... however, using the logic provided enabled me
to reach the required result.

Also, having achieved this, then gave me the confidence to explore further
hence the lines which clear both contents & formats of the prior calculated
area before copying down the new one which helps keep the sheet looking tidy.

So, once again, many thanks for your collective troubles pointing the way...
and I've filed the code provided away for next time that I need it

Regards
Colin