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 New Column Location

0

Last year, with this forums help, we were able to figure out an issue and improve the functionality of a spreadsheet I created.   

I only use the spreadsheet a few times a year and each time must import a new CSV file with updated data.      The macros I use are working great and I can import the new CSV, filter the data and summarize the information which is exactly what I wanted to do.  However, I have no control over the column locations within the CSV file.  The column locations have moved so my macro pulls from the wrong column.    It is easy to edit and change the column location within the macro.  But, with over 250 columns finding the new column locations is a huge pain. 

I'm reaching out to the forum for a solution.   I listed on the summary page all column names I use so is there a formula / macro that will display the column location for a column name from one sheet on a different sheet.  Such as…

(Summary Sheet)

 Column Name                       Column Location on Filtered Sheet

Order_number                      DE (Filtered Sheet) 

Order_date                             HB (Filtered Sheet)

Order_shipping_price                        T (Filtered Sheet)

Thank you

excel
MS Office Version: 2013
posted by: Why Me (rep: 4) on Fri Mar 29, 2019 at 12:31 pm
Answer
Discuss

Answers

0

Assuming the cloumns have headings you could run a macro accross the heading row to record the loaction. Alternativly you could run the macro and create a named range for each column. 

Here is a function I use to track down the column for a given heading in row 6

I pass in the sheet name, heading string, and If I need to get the column number or letter - defaults to number if you ommit the last field

Function Get_col(Sheet, parameter, Optional AorN = "N")
    Dim c
    Dim Get_col_address
    Dim ColRow
    With Sheets(Sheet).Rows(6)
        Set c = .Find(What:=parameter, LookIn:=xlValues, _
                LookAt:=VBA.Trim(xlWhole), SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    End With

    If Not c Is Nothing Then
        Get_col = c.Column 'returns column number by default
        If AorN = "A" Then 'or returns absolute letter
            Get_col_address = c.Address
            ColRow = Split(Get_col_address, "$")
            Get_col = ColRow(1)
        End If
    End If

End Function
posted by: k1w1sm (rep: 197) on Sun Mar 31, 2019 at 5:25 pm
Discuss

Discussion

Hi, Thank you for the answer.  Can you give me an example of a macro to build from?

Thank you
Why Me (rep: 4) Apr 2, '19 at 2:26 pm
Not able to do this. currently. I usualy use a "find" and then get the address fromthe result.  
k1w1sm (rep: 197) Apr 2, '19 at 5:20 pm
Add to Discussion


Answer the Question

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