VBA - Copy Specific Formulas

[smitpau]

Hi,

I'd like some help please with this situation below.

The VBA code works to copy months back a column to free space for future months.
It has linked cells references to other workbooks that should be converted to absolute references before copying to ensure it still references the same cell.
This should not apply to =SUM formulas as they should relate to the column they are on.

At the moment I manually select the range with the linked cells remove the = sign copy and paste then reinsert the equal sign.

This does work but selecting all the specific ranges takes time and is fragile (one new rows messes it up).

Ideally a solution with a single range that can be selected which could somehow use absolute references copy paste for linked cells but normal copy paste for the =SUM formulas.

Hope I explained that clearly.

Thanks for reading!

 

[smitpau]

Was thinking something along the lines of if the cells aren't bold then replace the = sign (as the =SUM are all in bold), but it only seems to work as a single TRUE/FALSE it doesn't apply it across the range in a dynamic array type style.

Like this:
If Range("F4:F5").Font.Bold = False

 

[smitpau]

To simplify this basically believe I'm after a looped replace if cell is bold VBA code.

 

[smitpau]

Something like this is where I'm currently at (doesn't work yet)

Dim Rng As Range, cell As Range
Set Rng = Range("F4:F5")

For Each cell In Rng
If Font.Bold = True Then
cell. Replace What:="=", Replacement:="*", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Next cell

End If
End Sub

 

[smitpau]

Getting slightly closer

Sub Test()

Dim rng As Range, cell As Range
Set rng = Range("F4:F5")

For Each cell In rng
If cell.Font.Bold = True Then
cell.Value = Replace(What = "=", Replacement = "x", LookAt = xlPart)
End If
Next cell

End Sub