Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Find Column

0

A spreadsheet I created was improved greatly with the help of this forum.  I only use this spreadsheet once a year for tax purposes and each year I try to improve it.   

Two CSV files are imported into separate sheets within one spreadsheet.  The data is filtered and summarized on different sheets. 

The issue, each year some columns are moved to different column letters.  Thus, my macro pulls in different data then the macro must be updated to pull in the correct data.  This is not a big deal but one CSV file has over 300 columns so I must search for the correct column letter then update the macro. 

What I want to do, add to my Summary page a list showing current column letters for required data columns plus show the column letter for the new imported CSV file.  This way, a quick glance and I can determine if the macro requires an update and also instantly see the new column letters without checking over 300 columns.

For Example…

                                    Current           New CSV Import

Order_date                 Column A:       Column B:

Order_invoice             Column D:       Column E:

Order_amount           Column: IZ:     Column KD:

I found several methods including some on this forum but either the method did not work for me or was way above my pay grade.   I only need a simple method / formula.  One formula…. =MATCH("order_date",Data!A1:KM1,0) brings in the column number but I can't figure out how to convert the column number to the Column letter.  I realize I can use column numbers in my macro but I'm use to using column letters and prefer to keep it that way. 

Any suggestions to convert a column number to column letter or a totally different method?

Thank you

excel
MS Office Version: 2013
posted by: Why Me (rep: 4) on Sat Mar 7, 2020 at 9:45 am
Answer
Discuss

Answers

0
Selected Answer

Convert column letter to column number with this formula.

=Column(D1)

=COLUMN(), without any reference cell will return the number of the column in which the formula resides. If you add a cell reference between the brackets the returned number will be that of the column of the reference. The row number is insignificant and will be ignored. You can use relative and absolute cell addressing if you want to copy the formula to other cells.

Converting a column number to an alphabetical column ID is a lot harder. Excel doesn't need that because Excel uses column numbers and all required conversion is from what you enter to what Excel needs, meaning alphabetical to numeric. However, here is a UDF (User Defined Function) that would do the job.

Function ColumnID(Clm As Long) As String
    ColumnID = Split(Columns(Clm).Address(0, 0), ":")(0)
End Function

Call the function from the worksheet like = ColumnID(54) where 54 is the column number.  You can build the function into formulas (dropping the leading equal sign). The only problem is that once you know the column number you can usually use that directly and converting it to an alphabetical ID just complicates your formulas. But the function may help you identify columns for manual processing.

posted by: Variatus (rep: 4889) on Sat Mar 7, 2020 at 9:59 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login