Sam, This is really great to know.... you got it working tht's the best thing..
The code for find and replace can be reduced to few lines.. but it's fine as long as it serves the purpose. :) Cheers, Anish On Tue, Jul 19, 2011 at 5:19 PM, Samuel Murray (Afrikaans translator) < afrika...@gmail.com> wrote: > Anish Shrivastava wrote: > > > Anyways, I will give you another macro which will search in each cell on > the sheet and if it finds a value then it will populate cells address at the > begining for the text but this may lead to problem with formulas. > > Your latest macro actually stops and gives me a type mismatch error > when it encounters a formula. > > However, I don't care about formulas as long as the formulas remain > "intact" (even if commented out somehow), so I was able to make your > macro work by first doing a find/replace that replaces "=" with "'=". > > So the revised macro is this: > > Sub test1() > Dim cel As Range > Cells.Replace What:="'=", Replacement:="''=", LookAt:=xlPart, _ > SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, > _ > ReplaceFormat:=False > Cells.Replace What:="=", Replacement:="'=", LookAt:=xlPart, > SearchOrder _ > :=xlByRows, MatchCase:=False, SearchFormat:=False, > ReplaceFormat:=False > For Each cel In ActiveSheet.UsedRange > If cel.Value <> "" Then > cel.Value = "[[[" & ActiveSheet.Name & "|" & Split(cel.Address, "$") > (1) & "|" & cel.Row & "]]]" & cel.Value > End If > Next cel > End Sub > > Sorry, I don't know how to write proper code, so the find/replace was > just recorded. > > This actually works for my purposes: the cell references appear in the > sharedStrings.xml of the unzipped XLSX file and can be manipulated > from there using plaintext editing. > > Thanks again > Samuel > > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
