Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace Comments
I have replace comments I did in a spreadsheet, unfortunatly it is
about 800 of them . I can find them easily with find function - comments but then the replace doesn't work for comments,only for formulas. Is there a way round this? I might have to also resize the box to a bigger size. Any suggestions I would be very grateful!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and replace Comments
If you're using xl2k or higher, you can use:
Option Explicit Sub testme01() Dim FoundCell As Range Dim FindWhat As String Dim WithWhat As String Dim lArea As Long FindWhat = "asdf" WithWhat = "QWER" Do Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _ After:=ActiveCell, _ LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.Comment.Text _ Replace(expression:=FoundCell.Comment.Text, _ Find:=FindWhat, Replace:=WithWhat, Start:=1, _ Count:=-1, compa=vbTextCompare) 'from Debra Dalgleish's site (from Dana DeLouis' code) 'http://contextures.com/xlcomments03.html#Resize With FoundCell.Comment .Shape.TextFrame.AutoSize = True If .Shape.Width 300 Then lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 ' An adjustment factor of 1.1 seems to work ok. .Shape.Height = (lArea / 200) * 1.1 End If End With End If Loop End Sub ===== If you're using xl97 or below, you'll have to make changes--there is no Replace() function--it was added in xl2k. It can be replaced with application.substitute(), but that's case sensitive, so you'll have to be more careful and match case--in the .find, too. Deba wrote: I have replace comments I did in a spreadsheet, unfortunatly it is about 800 of them . I can find them easily with find function - comments but then the replace doesn't work for comments,only for formulas. Is there a way round this? I might have to also resize the box to a bigger size. Any suggestions I would be very grateful!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Worksheet Functions | |||
How do you find and replace text in autoshapes or comments | Excel Discussion (Misc queries) | |||
Find and replace for comments in excel 2003 | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |