Excel Macro to find perticular column name

[Closed]
Report
-
Posts
1
Registration date
Tuesday March 8, 2011
Status
Member
Last seen
March 8, 2011
-
Hello,

Ii have excel having column names in row#1.

I want to write excel macro that will look for column name anything that had 'ID' or 'AMT' in it,and should format whole column (except 1st row which has name) in 0000-000-00 for ID and $ 000,000,00.00 for AMT.

So that time consumed in formatting each column will be reduced.

I would greately appriciate if someone knows solution about it.

Thanks in advance.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
before tampering with data save the file somewhere else safely for retrieval if necessary



try this macro

Sub test()
Dim j As Integer, cfind As Range
Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="ID", lookat:=xlWhole)
j = cfind.Column
Columns(j).NumberFormat = "000-000-000"
Set cfind = ActiveSheet.UsedRange.Cells.Find(what:="AMT", lookat:=xlWhole)
j = cfind.Column
Columns(j).NumberFormat = "000,000,00#.00"
End Sub
Hi,
Yaa this code is working fine. Thanks a lot for this.

But in my case -
1) Column is not always ID, it can be ID1,ID_1,1_ID.
So I want to search columns with key like 'ID' and then format
2) Also ID column will always have alphanumeric values like 123CD6789 and I want it as 123-CD6-789.NumberFormat doesn't work for alphanumeric.

Can you please help?
TEXT Number TEXT Number Number TEXT Number
ID_1 AMT_1 ID1 AMT1 AMT2 1_ID 1_AMT
1A3456789 1 2B3456789 12.23 0 3B3456789 98765

Output after macro execution should be-
1A3-456-789 $1.00 2B3-456-789 $12.23 $0.00 3B3-456-789 $98,765.00
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
try in what:= like this

what:="*id*

and see whether you succeed.

star sign before and after is widcard with "ID" in between.
wild cards some tiems succeed and some time fail
Posts
1
Registration date
Tuesday March 8, 2011
Status
Member
Last seen
March 8, 2011

Weldon job dear.

http://www.maxglim.com