Samuel, It was my assumption that your sheet would be the same as my example sheet which I used for macro. However, I feel sorry that it didnt help you.
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. No matter you put the additional text in the begining or at the end of the cell which contains any formula. Additional text will break the formula. Also please note that when we insert the cell's address within it , it would be the actual address. Suppose if there is any value "TEST" in cell "B16" on sheet1, the macro would make it "[[[Sheet1|B|16]]]TEST". Let me know if this result is fine with you. Let me know if it sounds fine with you, for now you can try the below code. It will work with all the cells no matter where your text is. *Sub test1()* *Dim cel As Range* *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* ** *Note :- I can understand completely that you cannot share the sample sheet with me ;)* Cheers!! Anish On Sun, Jul 17, 2011 at 6:38 PM, Samuel Murray (Afrikaans translator) < afrika...@gmail.com> wrote: > Anish Shrivastava wrote: > > > Please have a look on the attached file. This might be helpful. > > > > I tried it only on one column since I dont know in how many columns you > will have the data. If you could attach a sample sheet for your data, I can > make it more feasible. > > Thanks, Anish, for your answer. My apologies for not responding > sooner. I tried your macro on the day that you sent it, but it had a > number of problems (and I also discovered some other things with > regard to my needs). > > I thought that it would be okay to add the cell reference to the end > of cells, but that actually caused certain formula cells to break. It > would be better to place the cell reference at the start of the cell. > > Your macro stops when it reaches an empty cell (even if that is not > the last cell). > > From what I could see from your macro, the macro does not actually > query a cell to find out what its position is, but just assumes that > the first cell is in row 1, the second cell is in row 2, etc. I > wonder if this procedure might break if some cells are merged (for > then the third cell may be in row 4 already, if cell 2 and 3 are > merged). > > I tried to get your macro to work with both rows and columns by > nesting two loops, but I'm no expert at Excel macros and I could not > get the syntax to work. Bummer. :-) Though even if I did get it > working, I suspect that the macro would not work because of the issue > with merged cells. > > My spreadsheets typically have tens or hundreds of rows and columns, > and some of the cells may be merged, so any solution would have to > take that into account. Unfortunately I can't send you an example > file owing to confidentiality reasons... but any Excel file of e.g. a > financial report should be a good example. > > Possible alternative approach > > An alternative approach (I'm not sure if this would have any bearing > on how easy it would be to write a macro for it) would be that I add a > nonsense string in front of each cell, and that the macro replaces > that with the cell reference. Adding e.g. "XYZXYZ" to the front of > each cell would be simple (just do find/replace on the XML in the XLSX > file). > > So, if you had a worksheet in which each cell contains "XYZXYZ" > somewhere in the cell (or at the start of the cell), would it be > possible to do a type of find/replace that replaces the "XYZXYZ" with > the cell's reference (e.g. in the format [SheetName|Row|Col])? > > It would have to be the cell's actual reference (i.e. the cell must be > queried to find out its position), and not just an assumed reference > based on the number of rows or columns from the A1 position. > > Or would that have no effect on how easy/difficult it would be to > write this macro? > > Thanks > 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
