Previous Topic Next Topic

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.

Related:

3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
saurabh
Mar 7, 2011 at 10:23 PM
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
saurabh
Mar 8, 2011 at 09:36 AM
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
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
Answer 3 / 3
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

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses