I have this really strange AutoFill problem.

Background:
I have a template (xls) which basically contains a sheet ("Main") with
one row with links to a another worksheet( within the same workbook of
course ) which we can call "data".
The row in the "Main" sheet contains 65 cells with links and formulas,
all based on the "data" sheet. So in order to populate the Main sheet I
use AutoFill ( made with VBA ) to fill as many rows as there are data
in the data sheet.
Example: If there are 1000 rows in sheet "data", then 1000 rows in the
"Main" sheet will be Autofilled.

Code for autofilling:
Range(strRangeStartLeft).Select // For instance row 1
Cells.Replace What:="X1", Replacement:="X2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range(strRangeStartLeft + ":" + strRangeStartRight).Select //
select the Range to Autofill
strRange = strRangeStartLeft + ":" + strRangeEnd
Selection.AutoFill Destination:=Range(strRange)

Problem:
All formulas are working perfectly in all cells in the way that they
are updated meaning that e.g formula =IF(AX6=0;0;AX6-(AC6*AU6)) will on
row 7 of course then be =IF(AX7=0;0;AX7-(AC7*AU7)) and so on...
HOWEVER! On row 10 and cell BA all the sudden the formula is
=IF(AX20=0;0;AX20-(AC10*AU10))
And then its incremented until row 20 and then the formula is correct:
=IF(AX20=0;0;AX20-(AC20*AU20))

Could someone please help me and explain this to me? how come one
single cell isn't Autofilled properly and with this extremely stange
pattern? The remaining 64 cells ( From A to BM ) works perfectly even
if I Autofill up to 10.000 rows. How could one single cell "have a life
of its one"?

Im all out if ideas how to correct or track down whats going on.

Please help me.

/Mattias

I expect you rearranged some rows, possibly by sorting. You may
be able to fix by copying the good formula at or near the top downward.

As it is your code is filling in specific addresses.
You might take a look at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message oups.com...
I have this really strange AutoFill problem.

Background:
I have a template (xls) which basically contains a sheet ("Main") with
one row with links to a another worksheet( within the same workbook of
course ) which we can call "data".
The row in the "Main" sheet contains 65 cells with links and formulas,
all based on the "data" sheet. So in order to populate the Main sheet I
use AutoFill ( made with VBA ) to fill as many rows as there are data
in the data sheet.
Example: If there are 1000 rows in sheet "data", then 1000 rows in the
"Main" sheet will be Autofilled.

Code for autofilling:
Range(strRangeStartLeft).Select // For instance row 1
Cells.Replace What:="X1", Replacement:="X2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range(strRangeStartLeft + ":" + strRangeStartRight).Select //
select the Range to Autofill
strRange = strRangeStartLeft + ":" + strRangeEnd
Selection.AutoFill Destination:=Range(strRange)

Problem:
All formulas are working perfectly in all cells in the way that they
are updated meaning that e.g formula =IF(AX6=0;0;AX6-(AC6*AU6)) will on
row 7 of course then be =IF(AX7=0;0;AX7-(AC7*AU7)) and so on...
HOWEVER! On row 10 and cell BA all the sudden the formula is
=IF(AX20=0;0;AX20-(AC10*AU10))
And then its incremented until row 20 and then the formula is correct:
=IF(AX20=0;0;AX20-(AC20*AU20))

Could someone please help me and explain this to me? how come one
single cell isn't Autofilled properly and with this extremely stange
pattern? The remaining 64 cells ( From A to BM ) works perfectly even
if I Autofill up to 10.000 rows. How could one single cell "have a life
of its one"?

Im all out if ideas how to correct or track down whats going on.

Please help me.

/Mattias