Excel Macro to find perticular column name

Closed
Saurabh - Mar 3, 2011 at 08:36 PM
soniamike732
Posts
1
Registration date
Tuesday March 8, 2011
Status
Member
Last seen
March 8, 2011
- Mar 8, 2011 at 10:10 AM
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

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Mar 4, 2011 at 04:49 AM
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
0
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?
0
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Mar 8, 2011 at 01:51 AM
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
0
soniamike732
Posts
1
Registration date
Tuesday March 8, 2011
Status
Member
Last seen
March 8, 2011

Mar 8, 2011 at 10:10 AM
Weldon job dear.

http://www.maxglim.com
0