Replace Function Not Replacing Values Within A Range

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Code: 
With ws_stsked
    dt_row = Application.WorksheetFunction.Match(CLng(Date), .Columns(1), 0)
    elim = Application.WorksheetFunction.VLookup(FullName, ws_rstr.Range("E:H"), 4, False)
    rplc = str4
    Set rng_scour = .Range("B" & dt_row & ":HA375")
    With rng_scour
        With .Cells
            .Replace What:=elim, Replacement:=rplc, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        End With
    End With
End With

The idea of this code is to replace all the values of 'elim' found with the value of 'rplc' within the range set to 'rng_scour' (B & dt_row : HA375) of worksheet alias ws_stsked.

I don't get any errors, but nothing is being replaced. I suspect my approach my be wrong with the use of .cells within the range.

Any suggestions?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
try (untested)

Code: 
[COLOR=#ff0000]Dim Cel As Range[/COLOR]
With ws_stsked
  dt_row = Application.WorksheetFunction.Match(CLng(Date), .Columns(1), 0)
  elim = Application.WorksheetFunction.VLookup(FullName, ws_rstr.Range("E:H"), 4, False)
  rplc = str4
  Set rng_scour = .Range("B" & dt_row & ":HA375")

  [COLOR=#ff0000]For Each Cel In rng_scour[/COLOR]
       [COLOR=#ff0000]Cel = Cel.Replace[/COLOR](What:=elim, Replacement:=rplc, LookAt:=xlPart, MatchCase:=False)
  [COLOR=#ff0000]Next Cel
[/COLOR]
End With
 
Last edited:
Upvote 0
Your two internal with blocks are pointless, but the code itself looks fine. Are you sure the search value actually exists?

All you need is:

Code: 
rng_scour.Replace What:=elim, Replacement:=rplc, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
Upvote 0
Thank you both for helping out.
It turned out that the worksheet in which the replacement process applied was protected. For anything to work, I had to unprotect the worksheet first.
I found it unusual I didn't get an error message.
 
Upvote 0
I continue to have a problem with this concept. Help?

Rich (BB code): 
                With ws_stsked
                    .Unprotect
                    d3 = Application.WorksheetFunction.Match(CLng(Date), .Columns(1))
                    Set rng_d3 = .Range("B" & d3 & ":HA" & d3)
                    rng_d3.Replace What:=etx, Replacement:=etv, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
                    'For Each cel In rng_d3
                    '    cel = cel.Replace(What:=etx, Replacement:=etv, LookAt:=xlPart, MatchCase:=False)
                    'Next cel
                    cnt_etx = Application.WorksheetFunction.CountIf(.Cells, etx)
                    cnt_etv = Application.WorksheetFunction.CountIf(.Cells, etv)
                    MsgBox etv & " has been scheduled " & cnt_etv & " shifts."
                    .Protect
                End With

No errors, but the instances of 'etx' (WPStudent3) are not being replaced with the value etv (Student).
The value of "WPStudent3" exists in the worksheet, from row 93 (d3) to the end 106 times.

I also tried the routine in green as an alternative and I got the same results. The worksheet is unprotected.
 
Upvote 0
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
364​
[/td][td]BEFORE[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
365​
[/td][td]etx student1[/td][td]etx student101[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
366​
[/td][td]etx student2[/td][td]etx student102[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
367​
[/td][td]etx student3[/td][td]etx student103[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
368​
[/td][td]etx student4[/td][td]etx student104[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
369​
[/td][td]etx student5[/td][td]etx student105[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
370​
[/td][td]etx student6[/td][td]etx student106[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
371​
[/td][td]etx student7[/td][td]etx student107[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
372​
[/td][td]etx student8[/td][td]etx student108[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
373​
[/td][td]etx student9[/td][td]etx student109[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
374​
[/td][td]etx student10[/td][td]etx student110[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
375​
[/td][td]etx student11[/td][td]etx student111[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
364​
[/td][td]AFTER[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
365​
[/td][td]etv student1[/td][td]etv student101[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
366​
[/td][td]etv student2[/td][td]etv student102[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
367​
[/td][td]etv student3[/td][td]etv student103[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
368​
[/td][td]etv student4[/td][td]etv student104[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
369​
[/td][td]etv student5[/td][td]etv student105[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
370​
[/td][td]etv student6[/td][td]etv student106[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
371​
[/td][td]etv student7[/td][td]etv student107[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
372​
[/td][td]etv student8[/td][td]etv student108[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
373​
[/td][td]etv student9[/td][td]etv student109[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
374​
[/td][td]etv student10[/td][td]etv student110[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
375​
[/td][td]etv student11[/td][td]etv student111[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Use @RoryA method - it is efficient
Code: 
Sub ReplaceValues()

    Dim Cel As Range, ws_stsked As Worksheet, elim As String, rplc As String, dt_row As Long
    Set ws_stsked = Sheets(1)
    elim = "etx":   rplc = "etv":
    
    ws_stsked.Range("B365:HA375").Replace what:=elim, replacement:=rplc, MatchCase:=True        'case insensitive would be  MatchCase:=False

End Sub
 
Upvote 0
I have no reason not to trust these solutions, and I thank everyone for their suggestions, but I just am unable to get this to work.

Yongle, based on your suggestion ....

Code: 
     With ws_stsked
                    .Unprotect
                    d3 = Application.WorksheetFunction.Match(CLng(Date), .Columns(1))
                    'Set rng_d3 = .Range("B" & d3 & ":HA" & d3)
                    ws_stsked.Range("B" & d3 & ":HA" & d3).Replace What:=etx, Replacement:=etv, MatchCase:=False
                    cnt_etx = Application.WorksheetFunction.CountIf(.Cells, etx)
                    cnt_etv = Application.WorksheetFunction.CountIf(.Cells, etv)
                    MsgBox etv & " has been scheduled " & cnt_etv & " shifts."
                    .Protect
                End With

.... I'm still not getting the replacements happening.

ws_stsked is recognized properly.
The sheet is unprotected.
etx = "WPStudent3" and it does exist a number of times in the range. I checked the length of that value in that worksheet to check to ensure there wasn't some invisible trailing space or anything, and their wasn't.
etv = "Martin"

I don't know what to do now if even you pros are stumped.
 
Upvote 0
and using your values

Code: 
Sub ReplaceValues()

    Dim Cel As Range, ws_stsked As Worksheet, etx As String, etv As String
    Set ws_stsked = Sheets(1)
    etx = "[COLOR=#ff0000]WPStudent3[/COLOR]"
    etv = "[COLOR=#006400]Martin[/COLOR]"
    
    ws_stsked.Range("B365:HA375").Replace what:=[COLOR=#ff0000]etx[/COLOR], replacement:=[COLOR=#006400]etv[/COLOR], MatchCase:=True        'case insensitive would be  MatchCase:=False

End Sub


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
364​
[/td][td]before[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
365​
[/td][td][/td][td]a string WPStudent3[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
366​
[/td][td][/td][td]a string WPStudent4[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
367​
[/td][td][/td][td]a string WPStudent5[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
368​
[/td][td][/td][td]a string WPStudent6[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
369​
[/td][td][/td][td]a string WPStudent7[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
370​
[/td][td][/td][td]a string WPStudent8[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
371​
[/td][td][/td][td]a string WPStudent9[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
372​
[/td][td][/td][td]a string WPStudent10[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
373​
[/td][td][/td][td]a string WPStudent11[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
374​
[/td][td][/td][td]a string WPStudent12[/td][td]a string WPStudent3 a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
375​
[/td][td][/td][td]a string WPStudent13[/td][td]a string WPStudent3 a string[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
364​
[/td][td]after[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
365​
[/td][td][/td][td]a string Martin[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
366​
[/td][td][/td][td]a string WPStudent4[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
367​
[/td][td][/td][td]a string WPStudent5[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
368​
[/td][td][/td][td]a string WPStudent6[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
369​
[/td][td][/td][td]a string WPStudent7[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
370​
[/td][td][/td][td]a string WPStudent8[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
371​
[/td][td][/td][td]a string WPStudent9[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
372​
[/td][td][/td][td]a string WPStudent10[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
373​
[/td][td][/td][td]a string WPStudent11[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
374​
[/td][td][/td][td]a string WPStudent12[/td][td]a string Martin a string[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
375​
[/td][td][/td][td]a string WPStudent13[/td][td]a string Martin a string[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
318
Linda Lo
L
R
  • Solved
VBA - Find Replace on selected cells
Replies
3
Views
375
rsulliva
R
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
307
dwalc
D
J
  • Solved
Assistance with excel macro
Replies
3
Views
249
dreid1011
dreid1011
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
293
Jiraya_00
J

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

Share this page

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
Back
Top