Autofill filled and failed 1

[hjgoldstein]

I have a routine running in a macro which uses Autofill to copy a formula from - say - Cell A2 in a sheet to all the cells below it in column A.

Yesterday the Autofill worked but all of the values were the same as the value in A2 although the copied formula correctly referred to each row. F9/recalculate didn't fix it.

I believe this was a processor/server/comms/????? error and not an Excel 2007 error as it hasn't happened again.

I don't expect any of you will have a suggestion to avoid this happening but I wanted to share!

Aspiring to mediocrity since 1957

 

[combo]

Formula? Code?

combo

 

[hjgoldstein]

    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RIGHT(RC[-11],9),RC[-9])"
    Selection.AutoFill Destination:=Range("O2:O" & lngLastRow)

worked fine then

    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=100-RC[-2]"
    Selection.AutoFill Destination:=Range("P2:P" & lngLastRow)

didn't.

Everything had worked fine until yesterday and then again today. I'm convinced it was a problem outside of Excel. I just wanted to rant.

Aspiring to mediocrity since 1957

 

[combo]

What is the result, what is different from you expect? Is the value of lngLastRow as you planned (test with adding a breakpoint or add MsgBox lngLastRow in your code)?
After running the piece of code you should have range P2:O[lngLastRow] filled with formulas, having calculation switched on their result depends on the contents of cells in columns D, F and N..

combo

 

[hjgoldstein]

Sorry, maybe I'm not making myself clear.

The problem was that every record in the filled field was showing the same data as the one from which the copy was taken.

Auto calculate was on and so one would assume that the values in each record would relate to the values in column N.

There was nothing wrong with the range just the recalculation of the results.

Aspiring to mediocrity since 1957

 

[combo]

Recalculation in excel is funny, it behaves like a virus. It can be set for the whole application, but this setting is stored in the workbook and can change application settings:
[ul]
[li] excel takes the settings of the first opened workbook,[/li]
[li] next it applies them to all other workbooks the user opens, whatever calculation was when they were saved,[/li]
[li] the current setting "infects" all workbooks that are saved in the meantime,[/li]
[li] automatic (default) recalculation is restored when you close all workbooks and open a workbook with automatic recalculation.[/li]
[/ul]

If it's not your case, do you have the code generated formulas and input values proper?
You can force recalculation by code too:
ActiveCell.Parent.Calculate
In case of big data the recalculation may take some time.


combo

 

[SkipVought]

on a lighter note, in the spirit of Rocky & Bullwinkle, with a deference to ALT and appropriate alliteration…

Better to have Autofilled and failed than never to have Autofilled at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hjgoldstein]

Thanks combo. you've just gotta love these "undocumented features" of all M$ products.

Skip, is that a taxi for you?

Aspiring to mediocrity since 1957

 

[SkipVought]

@hjgoldstein, sorry, you'll have to decipher that for me.

Perspiring evaporatively since 1942

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Wow, just now I found this:

http://support.microsoft.com/kb/214395

combo

 

[hjgoldstein]

Wow, it is documented.



Aspiring to mediocrity since 1957

 

[hjgoldstein]

So as I couldn't rely on the setting being at Auto for anyone who uses the procedure I put

Application.Calculation = xlCalculationAutomatic

in at the start. Problem solved.

Aspiring to mediocrity since 1957

 

[Deniall]

You can even get a bit more fancy by putting

OldCalcState = Application.Calculation
Application.Calculation = xlCalculationAutomatic

at the start, and

Application.Calculation = OldCalcState

at the end.