Need Macro to replaces the ## with the Column Heading

[krishhi]

Hi Guys,

Please check the excel sheet, I need a macro to work. where "##" should be replaces with the Column Heading in this case Heading is "RochesterNY" without last two letters i.e. "Rochester", where # should be replaces with the whole Column Heading i.e "RochesterNY".​

Here is the file link:

http://www.4shared.com/file/BNHtxzak/Lawyers_data_base.html

Any help?

Waiting for your kind reply.

Thanks,

 

[krishhi]

Bump

 

[krishhi]

Ok,

I got an excellent answer from another place. May be it will useful to some one.

Here is the code:

 [LEFT]Sub ChangeNums() 
    Dim col As Range, cell As Range 
    For Each col In ActiveSheet.UsedRange.Columns 
        For Each cell In col.Cells 
            If cell.Address <> col.Cells(1, 1).Address Then 
                cell.Value = Replace(cell.Value, "##", Left(col.Cells(1, 1).Value, Len(col.Cells(1, 1).Value) - 2)) 
                cell.Value = Replace(cell.Value, "#", col.Cells(1, 1).Value) 
            End If 
        Next cell 
    Next col 
    ActiveSheet.UsedRange.Columns.AutoFit 
End Sub [/LEFT]