VBA - Copy Specific Formulas

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
To simplify this basically believe I'm after a looped replace if cell is bold VBA code.
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 2, 2020
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,756
Members
415,854
Latest member
Tutu123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top