Changing Formula from absolute to Relative

[FishGuy]

Hey,

I have the code below which builds up an array of data and formulas the applies that array as a range. This is fine except is always comes out as absolute. I then got the code in at the bottom to find ranges of formulas and change them to relative after the data has rendered. This logic works fine on other worksheets but on this worksheet I get the error TypeMismatch when the line RdoRange.Areas(ranges).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(ranges).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative gets hit. Could anyone see what could be going wrong here?

Please Login or Register  to view this content.

[snb]

Why using R1C1 notation in the first place ?
You'd be better off with

Please Login or Register  to view this content.

The use of an array doesn't seem necessary.

If using the formula conversion method (which I do not recommend, see above) I'd try

Please Login or Register  to view this content.

[FishGuy]

Hi the reason we use the array is that is how we populate the xls from the database record set, apparently doing it by range is quicker than cell by cell so we first fill the array with the data then match the array to the range taking into account both data values and formulas in the same block.
Looking at your fill down method I think that could work in new reports by doing the formulas seperate, unfortunately we already have numerous reports (some quite complex) using the existing model. Even worse it has only just come to light that this absolute issue is causing a problem.

[FishGuy]

Please Login or Register  to view this content.

I tried the above and on the line RdoRange.Areas(ranges).Formula = Application.ConvertFormula(rng.Formula, xlR1C1, xlA1, xlRelative)

I still get the typr mismatch error.

[snb]

You didn't copy my code properly.

[FishGuy]

Please Login or Register  to view this content.

I am getting and application object not defined error with the above code on the line
rng.Formula = Application.ConvertFormula(rng.Formula, xlR1C1, xlA1,

[snb]

Please Login or Register  to view this content.

This 3-liner is sufficient, remove the other codelines.

[FishGuy]

Possibly getting closer, that code runs without error and the lines do get hit but for somereason when the worksheet is displayed the forluma is still all absolute.

e.g

=IF($C$18<>0,$E$18/$C$18,0)

and

=[Analysis.xlsm]Detail!$R$65536) + SUMIF(Private!$W$8:$W$65536, $AB$20 & "/Onsite", Private!$R$8:$R$65536) + SUMIF(Public!$W$8:$W$65536, $AB$20 & "/Public", Public!$R$8:$R$65536) + SUMIF('3Party'!$W$8:$W$65536, $AB$20 & "/3Party", '3Party'!$R$8:$R$65536)

[snb]

Please Login or Register  to view this content.

[teylyn]

FishGuy, please don't quote whole posts. Use the Quick Reply box instead of the Quote button to create a new post.

Thanks

[FishGuy]

That code has chqanged the formula to relative but also changed the whole formula.
for exapmle.
The formula I put into the cells
TempArray(Currenti, colSummaryInvoicedCore) = _
"=SUMIF( Invoiced!" & Range(Cells(intSheetStartRow, colInvoicedKey1), Cells(65000, colInvoicedKey1)).Address & _
", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Core""" & _
", Invoiced!" & Range(Cells(intSheetStartRow, colInvoicedSplitTotal), Cells(65000, colInvoicedSplitTotal)).Address & ")"

The above formula used to come out like below
=SUMIF( Invoiced!$U$8:$U$65000, $AB$17 & "/Core", Invoiced!$J$8:$J$65000)

However it now appears like below
=SUMIF( Invoiced!AA17:AA65009, AH26 & "/Core", Invoiced!P17:P65009)

See how it no longer compares all the range from the 8th to th 65k but instead sums from the line it is on in the worksheet. I have no idea why it has changed AB17 to AH26 and Colums U to AA and J to P?

[snb]

Unless you post an example worksheet it's no use referring to variables we have no notion of what they contain or what value they have.

[FishGuy]

I would have to post the whole work book.

Basically it is a summary sheet which is the first sheet of the workbook. The summary sheet lists employee names and various totals against their name in other columns and column U is a key column this also holds the employee name.

On the summary sheet in the example above the column is called 'core sales' as it is used to display the value of sales with a sale type of core for this reason 'core' is appended to the key in the formula.

the formula to fill this column looks up the data from a second workheet which displays all the detals of sales, this is the Invoiced sheet. The column we are summing up is column J which is the value of each sale. The invoiced sheet also has a key column which is made up of the name of the employee making the sale and the sale type this is Column AB. This allows us to match the key on the summary and sum up each persons sales from the Invoiced sheet where the sale type is of type for example 'Core'

[snb]

Interesting, but although most of the helpers in this forum can read minds, they lack the ability to read non-posted workbooks.

[FishGuy]

Ok I have uploaded a sample which should demonstrate whats going on.

Basically it currently works perfectly, but if a user then does a sort on the data then then because it is all absolute the formula start displaying the wrong data which is why i need to change it.

Please Login or Register  to view this content.

[snb]

=SUMIF( Invoiced!AA17:AA65009, AH26 & "/Core", Invoiced!P17:P65009)

is the same as
=SUMIF( Invoiced!$U$8:$U$65000, $AB$17 & "/Core", Invoiced!$J$8:$J$65000)

with an offset(9,6)

[FishGuy]

Im not quite sure what you mean.

When I use the code to change the formula to relative

the workbook then renders like the workbook here instead of like the one above.

[FishGuy]

I changed
Application.ConvertFormula cl.Formula, xlR1C1, xlA1, xlRelative
to
cl.Formula = Application.ConvertFormula(cl.Formula, xlA1, xlA1, xlRelative)
and now the majority of values come out ok
Except the ones in columns c,d,e on the summary sheet which now just say #VALUE

Thats the ones

Please Login or Register  to view this content.

[snb]

Did you overlook this one

[shg]

Wouldn't it be easier to form the addresses first, and then reuse them? Also, you can look at the addresses ...

Please Login or Register  to view this content.

[FishGuy]

Yes, in the end rather than change the formula after the sheet had been updated I found I could specify relative when building the formula by using the .address method.

Please Login or Register  to view this content.