Range.replace not working

Add-in Express™ Support Service
That's what is more important than anything else

Range.replace not working
Range.Replace ignors Range 
Michael Kaden


Guest


Dear Andrei,

the following:

testsheet = alera.AddinModule.CurrentInstance.GetActiveSheet
Replak = testsheet.Range("$D$25")
Replak.Interior.ColorIndex = 5
Repp = Replak.Replace(Formel, NewFom)

Change the colour of D25 to blue as intended
However it replaces Formel with NewForm on the whole sheet - it does ignore the Range
can you help?

thank you & kind regards

Michael
Posted 09 Feb, 2018 15:27:25 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Michael,

this macro works fine for me in Excel 2016 and Excel 2010 

Sub dfgdfgds()
Dim r As Excel.Range
Set r = ActiveSheet.Range("$C$5")

Dim res As Boolean
res = r.Replace("=Now()", "=A1")
'Stop
End Sub



Andrei Smolin
Add-in Express Team Leader
Posted 12 Feb, 2018 03:47:45 Top
Michael Kaden


Guest


Dear Andrei,

I use it in an AddIn not a Macro. I send the sample complete project to the support address. Click code is as follows:

Private Sub replace_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles replace.OnClick
On Error GoTo Errorhandler
Dim r As Excel.Range
r = GetActiveSheet.Range("$C$5")
r.Replace("Old", "New")
Exit Sub
Errorhandler:
MsgBox(Err.Number, Err.Description, Err.Source)
Resume Next
End Sub


Instead of just replacing "Old" in cell $C$5 it replaces "Old" on the total worksheet.


thank you & kind regards

Michael
Posted 12 Feb, 2018 06:25:52 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
I reproduce this nice issue in Excel 2016. Investigating.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Feb, 2018 07:33:23 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
This is an Excel issue. If you open the Find dialog, expand Options, choose Within: Workbook, close the dialog and invoke your code, the replacement is done in the workbook. Tomorrow, I'll spend more time on looking for a workaround. Also, I'll publish a message on the Excel for Developers forum and post a link here.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Feb, 2018 11:29:49 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your help.

The workaround is not so difficult. one can just use Find instead of Replace in the following manner:

Private Sub UseFind_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles UseFind.OnClick
On Error GoTo Errorhandler
Dim loopnu As Integer = 0
Dim allRange, findrange As Excel.Range
Dim firstfind As Excel.Range = Nothing
Dim xlSheet As Excel.Worksheet = GetActiveSheet()
allRange = xlSheet.UsedRange
findrange = allRange.Find("Old", , Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
While Not findrange Is Nothing
loopnu = loopnu + 1
If loopnu > 50 Then
Exit While
End If
If firstfind Is Nothing Then
firstfind = findrange
ElseIf findrange.Address = firstfind.Address Then
Exit While
End If
If findrange.Address = "$C$5" Then
firstfind = findrange
findrange.Value = "New"
End If
findrange = allRange.FindNext(findrange)
End While
Marshal.ReleaseComObject(xlSheet) : xlSheet = Nothing
Exit Sub
Errorhandler:
MsgBox(Err.Number, Err.Description, Err.Source)
Resume Next
End Sub


If you put this "UseFind" Ribbon button in my sample project "CellSelect" it will work as intended. However, I was believing that replace will be much faster than a loop with Find. Please also note that I always put a max counter in While loops as I do not trust my ability to ensure the program is not caught inside the loop and I do not like to close Excel with the task manager :-)

Thank you & kind regards

Michael
Posted 12 Feb, 2018 14:37:08 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Michael,

That was stupid of me. Of course, you can modify the cell's value directly.

I've posted a description of the issue at https://social.msdn.microsoft.com/Forums/en-US/89f0df23-d53d-4ed7-a452-63f21d5f79a0/rangereplace-behaves-incorrectly?forum=exceldev. You can subscribe to that topic.


Andrei Smolin
Add-in Express Team Leader
Posted 13 Feb, 2018 05:10:44 Top