Hi, I'm trying to go through a worksheet, and look in the formulas of each of the cells, and if I find a particular string, change it. I'm basing my code on http://www.mrexcel.com/tip034.shtml.
Anyway, here's my code:
This works fine, but the problem is that it will show the sheet, show all the cells highlighted and the message box will just contain the contents of the matched cell's formula. I want to allow the user to see the actual matched cell, because I then want to give the user the option to decide whether to replace the contents or not.
Essentially I want to do the same thing that you get when you use the Replace menu function in the workbook (ie, go to Edit -> Replace), and instead of clicking on "Replace All", you click on "Find Next." As the user, you can then see what the next matched cell is, and if you choose to replace it, you can then click on "Replace."
I tried the line
but that still doesn't help because it just prints out the A1 style reference. Unfortunately, I don't know how to then allow the user to scroll to that reference or to display that reference on the screen. The other thing that worries me is that even when the msg box pops up, the contents of sheet are still highlighted, and if I add in a line that selects the matched cell based on cell.Address, I'm afraid that will screw up my For Each statement.
Thank you for any help you can provide me.
Anyway, here's my code:
Code:
ActiveWorkbook.Worksheets(i).Activate
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
For Each cell In Selection
RefPos = InStr(1, cell.FormulaR1C1, "6454", vbTextCompare)
If RefPos > 0 Then
Dim Result
Result = MsgBox(cell.FormulaR1C1, vbOKOnly)
End If
Next
This works fine, but the problem is that it will show the sheet, show all the cells highlighted and the message box will just contain the contents of the matched cell's formula. I want to allow the user to see the actual matched cell, because I then want to give the user the option to decide whether to replace the contents or not.
Essentially I want to do the same thing that you get when you use the Replace menu function in the workbook (ie, go to Edit -> Replace), and instead of clicking on "Replace All", you click on "Find Next." As the user, you can then see what the next matched cell is, and if you choose to replace it, you can then click on "Replace."
I tried the line
Code:
Result = MsgBox(cell.FormulaR1C1 & "; " & cell.Address, vbOKOnly)
Thank you for any help you can provide me.