Macro Assistance

Viking1221

New Member
Joined
May 25, 2017
Messages
31
I have a macro I need some assistance with. I want the macro to value any formulas that start with '=DBRW' as it is linked to another source. I have the following macro that works but it values out every formula.

Sub ConvertSourceFormulaToValues()
Dim ws As Worksheet, rng As Range
For Each ws In ActiveWorkbook.Worksheets
For Each rng In ws.UsedRange
If rng.HasFormula Then
rng.Formula = rng.Value
End If
Next rng
Next ws
End Sub
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,878
Office Version
365
Platform
Windows
How about
Code:
Sub Viking1221()
   Dim Ws As Worksheet
   Dim Cl As Range
   For Each ws In ActiveWorkbook.Worksheets
      For Each Cl In ws.UsedRange.SpecialCells(xlFormulas)
         If Left(Cl.Formula, 5) = "=DBRW" Then
            Cl.Value = Cl.Value
         End If
      Next Cl
   Next ws
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,878
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,089,971
Messages
5,411,581
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top