VBA Assistance Please!!

Desperationmode

New Member
Joined
Jan 8, 2019
Messages
3
[FONT=&quot]Hey Everyone,[/FONT]
[FONT=&quot]I have a workbook with 60+ worksheets, and inconsistent data ranges within each. I need to create VBA code to:[/FONT]

  • Search all worksheets in the workbook for specific text within a formula: Find = "*CODE"*
  • Replace any formula containing *CODE* with the value in the cell that the formula is calculating.
[FONT=&quot]There is a significant amount of data in these workbooks, so looping through cells would be inefficient and probably crash excel. Here's what I've got so far, a pretty basic find and replace... but is there any way to replace with values, versus another string!?[/FONT]
[FONT=&quot]Sub FindReplaceAll()[/FONT]
[FONT=&quot]Dim sht As Worksheet[/FONT]
[FONT=&quot]Dim fnd As Variant[/FONT]
[FONT=&quot]Dim rplc As Variant[/FONT]
[FONT=&quot]fnd = "*CODE*"[/FONT]
[FONT=&quot]'I'd like to replace any cell containing containing *CODE* in the formula, with the value that the formula is calculating[/FONT]
[FONT=&quot]rplc = "PLEASE BE VALUE IN CELL"[/FONT]
[FONT=&quot]For Each sht In ActiveWorkbook.Worksheets[/FONT]
[FONT=&quot]sht.Cells.Replace what:=fnd, Replacement:=rplc, _[/FONT]
[FONT=&quot]LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _[/FONT]
[FONT=&quot]SearchFormat:=False, ReplaceFormat:=False[/FONT]
[FONT=&quot]Next sht[/FONT]
[FONT=&quot]End Sub[/FONT]
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
welcome
Code:
Sub FindReplaceAll()
    
    Const IDENTIFIER As String = "CODE"
    
    Dim rng As Excel.Range
    Dim sht As Excel.Worksheet
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'I'd like to replace any cell containing containing *CODE* in the formula, with the value that the formula is calculating
    For Each sht In ActiveWorkbook.Worksheets
        Set rng = sht.Cells.Find(What:=IDENTIFIER, LookIn:=xlFormulas, LookAt:=xlPart)
        Do While Not rng Is Nothing
            rng.Value = rng.Value
            Set rng = sht.Cells.FindNext
        Loop
    Next sht
    Set sht = Nothing
    
    Application.EnableEvents = True
        
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,377
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top