CutPasteTranspose() does not check if any of the selected cells reference only cells within the selection.

[RaymondWise]

From Issue #8 discussion, if cells have formulas that reference the cells within the selection to be cut, the formulas will break.

Similarly, if you try to account for that, then any cells in the selection that reference cells outside of the selection will break.

For Each c In rngSelect
        c.Copy
        ActiveSheet.Cells(iORow + c.Column - iCCol, iOCol + c.Row - iCRow).PasteSpecial Transpose:=True
        c.ClearContents
    Next c

Additionally, trying to check for this would still leave fringe cases where a cell references both a cell within the selection and outside the selection.

ref. CutPasteTranspose

[byronwall]

Is there an example to test this on? I believe it works as intended in most cases.

My first thought is that things should work out in most circumstances. The code cuts each cell one-by-one so this ensures that all dependents are updated each time. This should happen regardless of whether or not the cell is in the moving range or somewhere else. I think this process should continue through the entire operation updating each cell and its dependents in turn.

Where it will break is if you are pasting the cut range in a way that overlaps the source. This will quickly lead to unpredictable results because you may overwrite a cell that is needed before it has time to "get out of the way".

The downside of doing the clear afterward, is you lose the ability for cells referring into the table to maintain their relationships. The cut approach works for this because it updates references in and out of the original range.

The original goal for this code was to take tables of calculations (e.g. monthly financial results for a year, split by budget category) and flip the table so that rows and columns swapped places. This was needed sometimes for visual reasons (trade horizontal scrolling for vertical), sometimes to help a later calculation, sometimes to then make a PivotTable, etc. I was doing a lot of this in financial spreadsheets where the cells being flipped were needed elsewhere in the spreadsheet, and I couldn't afford to break the spreadsheet just to get the layout I wanted.

[RaymondWise]

Sure, on a blank WS
A1 = 11
A2 = 2
A3 = sum(A1:A2)

Run the macro on that selection and pick anywhere to output, A3 keeps the A1:A2 reference

[byronwall]

Ah, I see it now. Excel will only update the multi cell range if the whole ranges moves at one time.

Well, that is an issue. And that is going to be tough to get around.

[RaymondWise]

The solution might be just to warn the user and allow them to abort.

We can handle the error of destination intersecting, but that might stop you from setting a target where there wouldn't be an error (it gets out of the way).

[byronwall]

Yea, I guess my testing on this was not that thorough. It might be possible to run through the formula, find the colons, grab the range, and check if it intersects the selection being moved. Then it can warn the user or abort.

A simple warning for now is probably adequate.

That might get into heavy string processing to pull out the formula ranges. I have been meaning to write a formula parser for Excel so maybe this is the chance to do it. There is something similar going on to parse the SERIES formula inside the bUTLChartSeries class: https://github.com/byronwall/bUTL/blob/master/src/code/bUTLChartSeries.vba#L95. That code is quite painful to look at though. Needs some cleanup.

[RaymondWise]

This would be an adequate test, I think -

Dim c As Range
    For Each c In rngSelect
        If Not Intersect(rngSelect, Cells(iORow + c.Column - iCCol, iOCol + c.Row - iCRow)) Is Nothing Then
            MsgBox ("Your destination intersects with your data")
            Exit Sub
        End If
    Next
    For Each c In rngSelect
        c.Cut
        ActiveSheet.Cells(iORow + c.Column - iCCol, iOCol + c.Row - iCRow).Activate
        ActiveSheet.Paste
    Next c

We might need to handle that error in the CopyTranspose routine as well.

[byronwall]

That works with the downside being that it might hit an error halfway through the operation with the input/output ranges in disarray.

A conservative check could be to see if a box that contains all of the input intersects with a box that contains all of the output.

[RaymondWise]

ah yeah, I forgot my next - that's supposed to be a closed loop prior to the next for each c loop

[byronwall]

Perfect. That will work nicely.

[RaymondWise]

This was completed in the last sync and can be closed.