VBA - Copy Specific Formulas

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
167
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
To simplify this basically believe I'm after a looped replace if cell is bold VBA code.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top