formulas not executing until workbook is saved

[Guest]

Hi All,

I was wondering if you can help me with this. I have a work book that when populated with data will insert formulas & copy them down until all populated rows have the formulas, then autofits all columns as im using the 3 sheets.

Anyhow, This was working for me fine yesterday but today after I wrote a different macro in a different module, assigned to a different command button. The formulas do get copied, and pasted all the way down but the correct values do not appear until you save the actual excel document. Then all is ok again & the correct values appear??

I know I can put in an autosave at the end of the macro but that's not really fixing the problem.
I can supply the code for the other macro if you want but it shouldn't have made a difference.
Also rebooted the sys, just in case the other macro left something in memory, no joy



My code for the macro is as follows:

Sub fill_formula_until_end()

' Starting on row 2 as Row 1 populated with Headings

Worksheets("Time & to be issued").Select



ActiveSheet.Cells(2, 12).Formula = "=VLOOKUP(J2,'Inc Categories'!$B$2:$D$10,3,FALSE)"
ActiveSheet.Cells(2, 13).Formula = "=VLOOKUP(J2,'Inc Categories'!$B$2:$F$10,4,FALSE)"
ActiveSheet.Cells(2, 14).Formula = "=G2/7.25"
ActiveSheet.Cells(2, 15).Formula = "=IF(H2=0,L2*N2,IF(H2=2,N2*M2,IF(H2=1,0)))"

' Copy Formula L2 & then paste down until empty Rows

Range("L2").Select
Selection.Copy

Row = 2
Do Until IsEmpty(Cells(Row, 1))
Cells(Row, 12).Select
ActiveSheet.Paste
Row = Row + 1
Loop
Row = Row - 1

' Copy Formula M2 & then paste down until empty Rows

Range("M2").Select
Selection.Copy

Row = 2
Do Until IsEmpty(Cells(Row, 1))
Cells(Row, 13).Select
ActiveSheet.Paste
Row = Row + 1
Loop
Row = Row - 1

' Copy Formula N2 & then paste down until empty Rows

Range("N2").Select
Selection.Copy

Row = 2
Do Until IsEmpty(Cells(Row, 1))
Cells(Row, 14).Select
ActiveSheet.Paste
Row = Row + 1
Loop
Row = Row - 1

' Copy Formula O2 & then paste down until empty Rows

Range("O2").Select
Selection.Copy

Row = 2
Do Until IsEmpty(Cells(Row, 1))
Cells(Row, 15).Select
ActiveSheet.Paste
Row = Row + 1
Loop
Row = Row - 1

' Use Autofit on all Cells

Cells.Select
Selection.Columns.AutoFit

Worksheets("Expenses").Select
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select

Worksheets("Others").Select
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select

Worksheets("Time & to be issued").Select
Range("A1").Select

End Sub

The only extra type of code I put in the other macro for speed issues was:

As the start:

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

But I finished with:

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

So that should undo the what I did at the beginning of the new macro.

Thanks a mil, for any1 that can help me on this

Sea

 

[Tom Ogilvy]

go into Tools=>Option, Calculation tab and make sure calculation is set to
automatic (rather than manual).

--
Regards,
Tom Ogilvy

Hi All,

I was wondering if you can help me with this. I have a work book that when

populated with data will insert formulas & copy them down until all
populated rows have the formulas, then autofits all columns as im using the
3 sheets.

Anyhow, This was working for me fine yesterday but today after I wrote a

different macro in a different module, assigned to a different command
button. The formulas do get copied, and pasted all the way down but the
correct values do not appear until you save the actual excel document. Then
all is ok again & the correct values appear??

 

[The Grinch]

you can always manually recalculate by hitting F

 

[Guest]

worked like a dream

go into Tools=>Option, Calculation tab and make sure calculation is set to
automatic (rather than manual).

--
Regards,
Tom Ogilvy


populated with data will insert formulas & copy them down until all
populated rows have the formulas, then autofits all columns as im using the
3 sheets.
different macro in a different module, assigned to a different command
button. The formulas do get copied, and pasted all the way down but the
correct values do not appear until you save the actual excel document. Then
all is ok again & the correct values appear??