How do I partially change the contents in every cell within a range?

[Demosthenes&Locke]

I have 2 sheets, the code is built to activate when a cell on one sheet is selected.

It then activates a different sheet, declares a range, and is supposed to use the replace method to replace a string within a formula.

                ActiveWorkbook.Sheets("BOM").Activate
                rngupdtd = "A20:H65"
                For Each cell In Range(rngupdtd)
                    cell.Replace What:=toreplacefilenow, Replacement:=toreplacefilewith, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
                    cell.Formula = cell.Formula
                Next cell

But it doesn't do anything.
When I try to copy the cell's contents using
thiscellis = cstr(cell.Value)
it says thiscellis is empty, so I believe that is the source of the problem,
somehow it's not giving me a replace error, but it's not actually accessing the cell's in the range and changing each one.

 

[mikerickson]

Instead of looping through each cell in Range(rngupdtd), try doing it all at once

ActiveWorkbook.Sheets("BOM").Activate
rngupdtd = "A20:H65"
Range(rngupdtd).Replace What:=toreplacefilenow, Replacement:=toreplacefilewith, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False

 

[Demosthenes&Locke]

What's really weird is even though I try your suggestion and still nothing happens on the sheet in question, the sheet with the code in it has spots that change (I copied some of the code from other pages and saw that it does it)

It's like activating doesn't change the sheet, even though with showing the updates on it shows the sheet changing.

 

[mikerickson]

It sounds like that code is in a sheet's code module. Try moving it to a normal module. Either that or fully qualifying the code.

rngupdtd = "A20:H65"
With ActiveWorkbook.Sheets("BOM")
    .Range(rngupdtd).Replace What:=toreplacefilenow, Replacement:=toreplacefilewith, LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
End With

 

[Demosthenes&Locke]

I moved it to a module, that was one of the problems. I appreciate your help, now I just need to find a faster way of doing it.