Copy/Paste Array Formula in Unique Absolute/Relative Application

[mohrx123]

Hello,

Please allow me to explain what I'm trying to accomplish first. I am taking advantage of absolute and relative references within a worksheet; however, when I copy/paste a "block" of rows (e.g. D20:Z58), I want the absolute references to copy and paste relative to the pasted block (and not link to their source block).

The workaround I use is this:

1) Copy my worksheet.

2) While in the copy, cut the block and paste it exactly 50 rows directly below. My formulas change as desired because the absolute and relative references do what they are supposed to do.

3) Highlight the block and run the following macro to find = signs and replace them with #^. This selection is then copied to the clipboard.
Sub EqualsToPound()
' ctrl+shift+c toggle copy cannot be used on multiple selectoins.
Selection.Replace What:="=", Replacement:="#^", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
End Sub

4) Go back into the original worksheet, select the same position I cut the block from in the copied worksheet, and paste

5) Then I run the following module macro while everything is still selected from the paste function previously.
Sub EqualsToPoundInverse()
'ctrl shift + v
Selection.Replace What:="#^", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

This method is simple, fast, reliable, and very powerful for running various scenarios and showing them side by side. However, any array formulas require me to go into the pasted cells that show !value and simply click in the formula bar and click ctrl+shift+enter to enter them as array. They then work fine. I tried finding open and closed curly brackets, replacing them with characters, and applying the above; however, the find function does not recognize the curly bracket that goes around an array formula.

Does anyone have a creative idea how to achieve the copy/paste intent that I do above with array formulas? One workaround is to simply not use array formulas but rather use helper columns and hide them. That works but is messy, and I do not like hidden columns.

Thank you,
J

 

[mohrx123]

Solved it!

Sub EqualsToPoundInverse()
'ctrl shift + v
Selection.Replace What:="#^", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Dim ce As Range
Set SelRange = Selection
For Each ce In SelRange
If IsError(ce.Value) Then
If ce.Value = CVErr(xlErrValue) Then
ce.FormulaArray = ce.Formula
Else
End If
Else
End If
Next ce

End Sub