Can't get Search/Replace to work within VBA

[darvistor]

Hello all,

I have an excel file that gets populated every month and resaved based on the current month/year. I need to routinely copy a range of data from the previous month's file into the current month's file. Within the copy range there are some formulas that use vlookups into another spreadsheet in the workbook. The problem I have is that I don't want the data to be linked to the previous month's file, which happens when I copy the formula. I tried to build a series of ranges to exclude the formulas, but I can't get the data to paste (can't paste multiple selections error) into the new spreadsheet.

One way I found around this was to copy all the data/formulas over. Then I can do a search and replace within the formulas and replace all instances of "[Prevmonth.xls]Products" with "Products", which is the worksheet the vlookup is linked to. The problem I have is that I can't get the search/replace to work within the VBA code.

I set a variable, replacewrd, equal to the string I want replaced.

Dim replacewrd As String
replacewrd = CStr("[" + pmonth + " " + s2name + "]" + "Products")        
        Cells.Replace What:=replacewrd, _
        Replacement:="Products", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

After the code finished, none of the replacements were made. I hit CTRL-H to bring up the search/replace menu and the fields were populated with the text I wanted to change to and from. I hit Replace All and then all the replacements were made. I want to avoid this last step somehow.

Any help would be greatly appreciated.
Thanks, Darv

 

[SkipVought]

Hi,

Have you looked at the value in replacewrd?

Is it waht you expect?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

Yes, I made the replacewrd a variable that would be able to handle the search/replace month after month as the file names change. I stepped into the code and checked the value of replacewrd right before the code I pasted above. The string that is formed is what I expected. That is why I can't figure this out. It just doesn't seem to execute the code from within VBA. Yet, if if I let the sub finish and then I open up the Replace menu as I stated the string for replacewrd appears in the search field and "Products" appears in the replace field.

I could live with hitting the Replace All button manually, but I don't trust the other 4 people who use this file to remember each time. There are around 860 replacements made when it finishes.

 

[electricpete]

Is it possible the sheet you are interested in was not the active sheet when you executed the code ? Perhaps add msgbox(activesheet.name) just ahead of this code to verify this.

 

[electricpete]

also since you mentioned multiple workbooks might be looking in the wrong one. Or perhaps there is some narrow default range established in a with statement ahead of this.

Maybe try
debug.print cells.parent.name ' should give sheet name
debug.print cells.parent.parent.name ' should give book name

 

[darvistor]

Hmm, thanks Pete. I will check this out when I return to work on monday. Initially this code gets executed from the first worksheet of the current month's file.

In the code I open the previous file, activate the Data Entry sheet, copy the data I want, switch to the current month's file, switch to the Data Entry sheet, then I beleive I select the first cell where the data will be pasted and finally paste the data. After the data is pasted, I run the replace code.

If I had selected the first cell by way of a range command (Range("H5").Select) could that be limiting the cells that the replace command looks in? What if I select the range of data that I just pasted in right before the replace code executes? I'll try this out at the inlaws with some sample code and see if that works.

 

[darvistor]

Yeah! I got it to work. The part about the narrow range got me thinking Pete. Because I selected the first cell where I wanted the data paste to start, that is the only cell that would have been affected, but this cell didnt have anything to replace. All I did was add the code to reselect the pasted data prior to executing the replace commmand and all the replacements were made correctly. Thanks! Darv

 

[electricpete]

Glad you got it to work.

But, I don't understand how cell or range selection in the sheet makes any difference. When I type in my immediate window:
? cells.parent.name
I get Sheet1 ... regardless of whether there are any cells selected or not.

So as long as Sheet1 is active and there is no with statement qualifying that cells refers to another specific range or sheet, I would have thought that cells refers to the entire active sheet (regardless of any selection).

What am I missing ?

 

[darvistor]

I was just as confused when I activated the sheet prior to the replace command and it still didn't work. I am not using any with statements so I just expected it to look at all the cells and do the replace. The only way I got it to work was to select the range of data where I knew the replacements needed to be made.