Changing Formula from absolute to Relative

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?

 'Outstanding - Other
         TempArray(Currenti, colSummaryOutstandingOther) = _
            "=SUMIF( Outstanding!" & Range(Cells(intSheetStartRow, colNotInvoicedKey1), Cells(65000, colNotInvoicedKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Other""" & _
            ", Outstanding!" & Range(Cells(intSheetStartRow, colInvoicedSplitTotal), Cells(65000, colInvoicedSplitTotal)).Address & ")" & _
            "+SUMIF( PostPay!" & Range(Cells(intSheetStartRow, colNotInvoicedKey1), Cells(65000, colNotInvoicedKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Other""" & _
            ", PostPay!" & Range(Cells(intSheetStartRow, colInvoicedSplitTotal), Cells(65000, colInvoicedSplitTotal)).Address & ")"
        
        'Outstanding - Total
         TempArray(Currenti, colSummaryOutstandingTotal) = _
            "=R" & intRow & "C" & colSummaryOutstandingCore & _
            "+R" & intRow & "C" & colSummaryOutstandingCoreSL & _
            "+R" & intRow & "C" & colSummaryOutstanding3Party & _
            "+R" & intRow & "C" & colSummaryOutstanding3PartySL & _
            "+R" & intRow & "C" & colSummaryOutstandingProduct & _
            "+R" & intRow & "C" & colSummaryOutstandingOther
        
        'Total
         TempArray(Currenti, colSummaryTotalCore) = "=R" & intRow & "C" & colSummaryInvoicedCore & "+R" & intRow & "C" & colSummaryOutstandingCore
         TempArray(Currenti, colSummaryTotalCoreSL) = "=R" & intRow & "C" & colSummaryInvoicedCoreSL & "+R" & intRow & "C" & colSummaryOutstandingCoreSL
         TempArray(Currenti, colSummaryTotal3Party) = "=R" & intRow & "C" & colSummaryInvoiced3Party & "+R" & intRow & "C" & colSummaryOutstanding3Party
         TempArray(Currenti, colSummaryTotal3PartySL) = "=R" & intRow & "C" & colSummaryInvoiced3PartySL & "+R" & intRow & "C" & colSummaryOutstanding3PartySL
         TempArray(Currenti, colSummaryTotalProduct) = "=R" & intRow & "C" & colSummaryInvoicedProduct & "+R" & intRow & "C" & colSummaryOutstandingProduct
         TempArray(Currenti, colSummaryTotalOther) = "=R" & intRow & "C" & colSummaryInvoicedOther & "+R" & intRow & "C" & colSummaryOutstandingOther
         TempArray(Currenti, colSummaryTotalTotal) = "=R" & intRow & "C" & colSummaryInvoicedTotal & "+R" & intRow & "C" & colSummaryOutstandingTotal

    

         Currenti = Currenti + 1
         
         End With
        
        intRow = intRow + 1
    Next
    
   
        Application.ScreenUpdating = False
        Application.Calculate
        Set Person = Nothing
        TheRange.Value = TempArray
         Dim RdoRange As Range
    Dim ranges As Long

     Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
              For ranges = 1 To RdoRange.Areas.Count
                RdoRange.Areas(ranges).Formula = _
                Application.ConvertFormula _
               (Formula:=RdoRange.Areas(ranges).Formula, _
                FromReferenceStyle:=xlA1, _
                ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

            Next ranges        Application.ScreenUpdating = True

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

Cells(1, 1) = "=sum(B1,C1,F1,K1,P1)"
Cells(1, 1).Resize(20).FillDown

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

For each rng in  RdoRange.Areas
   rng.Formula = Application.ConvertFormula(rng.Formula,xlR1C1,xlA1,xlRelative)
Next

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.

You didn't copy my code properly.

   TheRange.Value = TempArray
           Dim RdoRange As Range
           Dim Ranges As Range
           Dim rng As Range

            Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
              For Each rng In RdoRange.Areas
                rng.Formula = Application.ConvertFormula(rng.Formula, xlR1C1, xlA1, xlRelative)
                Next

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

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

Thanks

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'

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

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.

'Summary sheet
Const colSummarySalesPerson = 1
Const colSummarySalesTeam = 2
Const colSummaryDelivered = 3
Const colSummaryDeliveredCost = 4
Const colSummaryDeliveredMargin = 5
Const colSummaryDeliveredMarginPerc = 6
Const colSummaryInvoicedCore = 7
Const colSummaryInvoicedCoreSL = 8
Const colSummaryInvoiced3Party = 9
Const colSummaryInvoiced3PartySL = 10
Const colSummaryInvoicedProduct = 11
Const colSummaryInvoicedOther = 12
Const colSummaryInvoicedTotal = 13
Const colSummaryOutstandingCore = 14
Const colSummaryOutstandingCoreSL = 15
Const colSummaryOutstanding3Party = 16
Const colSummaryOutstanding3PartySL = 17
Const colSummaryOutstandingProduct = 18
Const colSummaryOutstandingOther = 19
Const colSummaryOutstandingTotal = 20
Const colSummaryTotalCore = 21
Const colSummaryTotalCoreSL = 22
Const colSummaryTotal3Party = 23
Const colSummaryTotal3PartySL = 24
Const colSummaryTotalProduct = 25
Const colSummaryTotalOther = 26
Const colSummaryTotalTotal = 27

Const colSummaryKey1 = 28

'Bookings
Const colBookingSalesPerson = 1
Const colBookingSalesTeam = 2
Const colBookingEvent = 3
Const colBookingEventType = 4
Const colBookingPractice = 5
Const colBookingVendor = 6
'Const colBookingSubject = 7
Const colBookingLocation = 7
Const colBookingStartDate = 8
Const colBookingStartPeriod = 9
Const colBookingDuration = 10
Const colBookingNumber = 11
Const colBookingSplit = 12
Const colBookingCustomer = 13
Const colBookingCostCalc = 14
Const colBookingRRP = 15
Const colBookingRevenue = 16
Const colBookingCost = 17
Const colBookingMargin = 18
Const colBookingMarginPerc = 19
Const colBookingAddDate = 20
Const colBookingStatus = 21
Const colBookingConfirmDate = 22

Const colBookingKey1 = 23

'Product
Const colProductSalesPerson = 1
Const colProductSalesTeam = 2
Const colProductCode = 3
Const colProductDescription = 4
Const colProductRRP = 5
Const colProductCostCalc = 6
Const colProductCost = 7
Const colProductMargin = 8
Const colProductMarginPerc = 9
Const colProductNumber = 10
Const colProductSplit = 12
Const colProductRevenue = 11
Const colProductCustomer = 13
Const colProductAddDate = 14
Const colProductStatus = 15
Const colProductConfirmDate = 16
Const colProductAddedBy = 17
Const colProductInvoiced = 18
Const colProductKey1 = 19

'Invoices/Credits

Const colInvoicedBookingSalesPerson = 1
Const colInvoicedBookingSalesTeam = 2
Const colInvoicedCourseProduct = 3
Const colInvoicedEventNumber = 4
Const colInvoicedLocation = 5
Const colInvoicedStartDate = 6
Const colInvoicedStartPeriod = 7
Const colInvoicedEventType = 8
Const colInvoicedSplitPercentage = 9
Const colInvoicedSplitTotal = 10
Const colInvoicedRevenueType = 11
Const colInvoicedBookNumber = 12
Const colInvoicedCustomerName = 13
Const colInvoicedAltRef = 14

Const colInvoicedInvoiceNumber = 15
Const colInvoicedWeekComencingDate = 16
Const colInvoicedInvoiceDate = 17
Const colInvoicedBookConfDate = 18
Const colInvoicedRevenueClassification = 19
Const colInvoicedLicenseNumber = 20
Const colInvoicedKey1 = 21

Const colNotInvoicedBookAddDate = 15
Const colNotInvoicedBookStatus = 16
Const colNotInvoicedBookConfDate = 17
Const colNotInvoicedWeekComencingDate = 18
Const colNotInvoicedExpectedInvoicePeriod = 19
Const colNotInvoicedRevenueClassification = 20
Const colNotInvoicedLicenseNumber = 21
Const colNotInvoicedKey1 = 22

=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)

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.

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

 'Delivered
        TempArray(Currenti, colSummaryDelivered) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingRevenue), Cells(65536, colBookingRevenue)).Address & ")"

       'DeliveredCost
        TempArray(Currenti, colSummaryDeliveredCost) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingCost), Cells(65536, colBookingCost)).Address & ")"

        'DeliveredMargin
        TempArray(Currenti, colSummaryDeliveredMargin) = _
            "=SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Company""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/RoomHire""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Private!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Onsite""" & _
            ", Private!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(Public!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/Public""" & _
            ", Public!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ") + SUMIF(3Party!" & Range(Cells(intSheetStartRow, colBookingKey1), Cells(65536, colBookingKey1)).Address & _
            ", " & Range(Cells(intRow, colSummaryKey1), Cells(intRow, colSummaryKey1)).Address & " & ""/3Party""" & _
            ", 3Party!" & Range(Cells(intSheetStartRow, colBookingMargin), Cells(65536, colBookingMargin)).Address & ")"

        'Delivered Margin %
         TempArray(Currenti, colSummaryDeliveredMarginPerc) = _
            "=IF(R" & intRow & "C" & colSummaryDelivered & "<>0,R" & intRow & "C" & colSummaryDeliveredMargin & "/" & "R" & intRow & "C" & colSummaryDelivered & ",0)"

Did you overlook this one