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

[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?


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


Waiting for your kind reply.

Thanks,

[Kenneth Hobs]

You can attach files here.

[VBA]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
[/VBA]

[krishhi]

You can attach files here.

[vba]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
[/vba]

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

Thank you once again.