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
