Why AutoFill of formulae not working 2

[shelby55]

Hi

I am using Office 2003.

I have an Excel sheet where columns A to Z are data fields imported from an Access database. Columns AA to DO are formulae fields based on data in cells A to Z.

The worksheet data is updated via MS-Query and I have the settings of "fill down formulas in columns adjacent to data" and this works with columns AA to DL but not DM, DN or DO.

Column DM is to track week number so cell DO3 is 1 and all subsequent cells in DO indicate: =IF(DN4="","",IF(WEEKDAY(DN3)=1,DM3+1,DM3))

Column DN is for a "show" version of the date and is:
=IF(A3="","",A3)

Column DO is for the "week ending" and is:
=IF(DN3="","",IF(WEEKDAY(DN3)=1,DN3,DN3+(8-WEEKDAY(DN3))))

I am not the original author of the workbook and I'm trying to automate it but I'm not VBA savvy. However, I think the original cell in DM1 having to be a number and formlae thereafter is causing the problem. How can I re-write that so it is a formula too (if you think that is my problem)?

Thanks very much.

 

[SkipVought]

Modify your formula to put 1 in the first row...
[tt]
=if(row()=3,1,your formula)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

First, that's 93 calculated fields!?! I strongly suspect that there's a better design for the spreadsheet. But ignoring that for the moment....

The "fill down formulas in columns adjacent to data" option is a helpful and often-overlooked feature of Excel. But it has its limitations.

What will happen if the current data set contains fewer records than the last data set? You would wind up with extra formulas.

So I think you'll be better served to use a more "bullet-proof" solution anyway.... I'll offer the following to get you started. If you need additional help with this, please create a new thread in forum707.

I'd first get rid of the formulas in all except the top-most row - that will avoid the problem I mentioned where the current data set contains fewer records than the previous data set.

Then, after the import, use code to fill down to the total number of rows that are now populated.

It will look sometime like this:

    dblAvailRows = ActiveSheet.Rows.Count
    dblAvailCols = ActiveSheet.Columns.Count
    Sheets("Data").Range("AA2:DO" & dblAvailRows).Delete shift:=xlUp
    '...
    'Existing code to update data table
    '...
    dblLstRow = Cells.Find( _
            What:="*", _
            after:=Cells(dblAvailRows, dblAvailCols), searchorder:=xlByRows, _
            searchdirection:=xlPrevious).row
    Sheets("Data").Range("AA2:DO" & dblLstRow).filldown

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Arrrgh!

Good to see some things never change - like Skip's nimble fingers beating me to the punch.

Howdy, Skip - work has been crazy, but I'm making an effort to come around here more often.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

What will happen if the current data set contains fewer records than the last data set? You would wind up with extra formulas.

Not so!

Fewer rows or more rows. Either way, this feature will adjust the formulas in adjacent columns.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Guys

John: I appreciate your efforts but Skip's simple but elegant code is exactly what I needed.

Thanks so much to both of you!!

 

[AnotherHiggins]

@SkipVought: I've been down this specific path before with Excel 2003 and I found this feature to be glitchy at best when dealing with imported data. Maybe I was just doing something wrong....

@shelby55: Glad you've got it sorted out. Good luck!

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I have use this feature in 2003 & 2007 without a glitch.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Not used it often but I'm generally with Skip on this - it does seem to work whether more or less records. tbh though I dislike querytables as I'm a control freak and would much rather have more control over the data and where / how it ends up so do nearly everything using ADO these days

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question