Replace Function Not Replacing Values Within A Range

[Ark68]

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?

 

[Yongle]

try (untested)

[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

 

[RoryA]

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:

rng_scour.Replace What:=elim, Replacement:=rplc, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

[Ark68]

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.

 

[Ark68]

I continue to have a problem with this concept. Help?

                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.

 

[Yongle]

Excel 2016 (Windows) 32 bit

	B​	C​
364​	BEFORE
365​	etx student1	etx student101
366​	etx student2	etx student102
367​	etx student3	etx student103
368​	etx student4	etx student104
369​	etx student5	etx student105
370​	etx student6	etx student106
371​	etx student7	etx student107
372​	etx student8	etx student108
373​	etx student9	etx student109
374​	etx student10	etx student110
375​	etx student11	etx student111

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B​	C​
364​	AFTER
365​	etv student1	etv student101
366​	etv student2	etv student102
367​	etv student3	etv student103
368​	etv student4	etv student104
369​	etv student5	etv student105
370​	etv student6	etv student106
371​	etv student7	etv student107
372​	etv student8	etv student108
373​	etv student9	etv student109
374​	etv student10	etv student110
375​	etv student11	etv student111

Sheet: Sheet1

Use @RoryA method - it is efficient

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

 

[Ark68]

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 ....

     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.

 

[Yongle]

I am not stumped

 

[Yongle]

and using your values

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

	A​	B​	C​
364​	before
365​		a string WPStudent3	a string WPStudent3 a string
366​		a string WPStudent4	a string WPStudent3 a string
367​		a string WPStudent5	a string WPStudent3 a string
368​		a string WPStudent6	a string WPStudent3 a string
369​		a string WPStudent7	a string WPStudent3 a string
370​		a string WPStudent8	a string WPStudent3 a string
371​		a string WPStudent9	a string WPStudent3 a string
372​		a string WPStudent10	a string WPStudent3 a string
373​		a string WPStudent11	a string WPStudent3 a string
374​		a string WPStudent12	a string WPStudent3 a string
375​		a string WPStudent13	a string WPStudent3 a string

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A​	B​	C​
364​	after
365​		a string Martin	a string Martin a string
366​		a string WPStudent4	a string Martin a string
367​		a string WPStudent5	a string Martin a string
368​		a string WPStudent6	a string Martin a string
369​		a string WPStudent7	a string Martin a string
370​		a string WPStudent8	a string Martin a string
371​		a string WPStudent9	a string Martin a string
372​		a string WPStudent10	a string Martin a string
373​		a string WPStudent11	a string Martin a string
374​		a string WPStudent12	a string Martin a string
375​		a string WPStudent13	a string Martin a string

Sheet: Sheet1

 

[Ark68]

Thank you ...