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

Demosthenes&Locke

Board Regular
Joined
May 11, 2010
Messages
93
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.
Code:
                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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
Instead of looping through each cell in Range(rngupdtd), try doing it all at once

Code:
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

Board Regular
Joined
May 11, 2010
Messages
93
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
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.

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

Board Regular
Joined
May 11, 2010
Messages
93
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,498
Messages
5,596,509
Members
414,073
Latest member
Contilly

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