PDA

View Full Version : Solved: Need Macro to Replace ## with the Column Heading Name



krishhi
09-20-2011, 04:23 AM
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?


I have nearly 120 Columns. Its very hard to find and replace.


Waiting for your kind reply.

Thanks,

Kenneth Hobs
09-20-2011, 07:31 AM
You can attach files here.

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

krishhi
09-20-2011, 08:54 AM
You can attach files here.

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


Millions of Thanks. You just made my day very beautiful.

Thank you once again.