How to find particular column name by using Excel Macro
Hello yesterday I faced one problem with my Excel. I have Excel shit which contains column names in row#1. Basically I am facing problem with excel macro. I want to write excel macro that will appear for column names no matter which that had 'ID' or 'AMT' in it, and be supposed to format complete column apart from 1st row which has name in 0000-000-00 for ID and $ 000,000,00.00 for AMT. So that time devoted in formatting every column will be concentrated. I would really appreciate if somebody be acquainted with solution about it. Thanks in advance.
Re: How to find particular column name by using Excel Macro
Hello please follow this macro I hope this will help you to solve your problem. Because some day before I was facing similar kind of problem them I follow this macro and by using this I solve my problem.
Sub tesmacr()
Dim i As Integer, cfindmacro As Range
Set cfindmacro = ActiveSheet.UsedRange.Cells.Find(what:="ID", lookat:=xlWhole)
i = cfind.Column
Columns(i).NumberFormat = "000-000-000"
Set cfindmacro = ActiveSheet.UsedRange.Cells.Find(what:="AMT", lookat:=xlWhole)
i = cfindmacro.Column
Columns(i).NumberFormat = "000,000,00#.00"
End Sub
I hope it will work for you.
Re: How to find particular column name by using Excel Macro
Hi, thank you for your quick response on my query. I try this code and its working fine with excel. But in my case basically column is not always ID; it can be ID1, 1_ID, ID_1. So I require searching columns with key like ID and then format. And in another case, in addition ID column will forever have alphanumeric values similar to 123CD6789 and I wish for it as 123-CD6-789.NumberFormat doesn't perform on behalf of alphanumeric.
Re: How to find particular column name by using Excel Macro
Hello guys I am facing similar kind of problem related to this macro. I am using Microsoft office 2007 in my system. And I am doing all my office work on this Excel. So this Excel is more important in my work. I don’t understand how to fix this ID related issues. I try to find about this on internet but not get much information on this topic. How I can write macro with any ID. Because, ID is not a big factor still it’s not performing. Some day before, I read seaware about the wild card in macro. Is it possible to use them with this? Can anyone tell me how you can use wildcard for searching? Help me thank you.
Re: How to find particular column name by using Excel Macro
Hello follow the following code by using this you can set wildcard with macro for your code. You have to use
what: = like this
what: ="*id*
and observe whether you achieve something. Basically when you are using star sign before and after is known as wildcard in the company of the ID in between. By using wild card some items achieve something and some time it’s not performing as your requirement. So I hope this will work for your thank you.
Re: How to find particular column name by using Excel Macro
When responsibility searches in Excel, you can make use of wildcard characters in the pattern of what you are trying to search with Excel. On the other hand, you cannot make use of them in the alternate text. These tips look at ways you are able to work around this limitation and utilize wildcards in your replacements. This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007. I hope now you are clear with your doubt about this wildcard processing with macro in Excel.