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
