How to Get the column number in ExcelReadSheetToArray

[RickB75]

Guys,

Just a quick question. I'm drawing blank on how to get the column number after using ExcelReadSheetToArray. I have the header columns set to specific names in different workbooks in excel. example: Vin, ID, Year, etc...  In some workbooks the vin column could be column 3 and some workbooks it could be in column 7. I know how to do an array search and find the string "Vin" but for the life of me I can't figure out how to get the column number after I find the string. I'm sure this is simple but I'm drawing a complete blank right now! Should I build a loop with a counter and after it finds the string use the position as the Column number? Is there an easier way than building a loop. A simple function to return the column number in the array.

Edited June 6, 2014 by RickB75

[water]

I'm not sure I fully understand the problem. But let me give it a try:

An Excel Worksheet consists of rows and columns starting with index 1 for columns and rows.

An array consists of rows and columns starting with index 0 or 1 (if the row/column count is returned in row 0) for rows and index 0 for columns.

So to map the column index of the array to the column number of Excel you simply add 1 to the array index.

[RickB75]

Water,

       Thanks for your reply. I apologize for my post sounding confusing.  This is what my goal is. I have multiple excel workbooks with vehicle data in the workbooks. The header (row #1) is dedicated for the vehicle info like Vin #, Make, Model, Trim, etc. In some workbooks the Vin # column could be column 3. In other workbooks it could be column 7. What I'm wanting to do is write a script that can read multiple layouts of different workbooks using the header data as a way to identify the data in the column. Then, I can do a ArraySearch for the word "Vin #" (I know it will show in position 1 because it's always on the 1st row. It's the column number that will vary)  in any one of the workbooks and once I find it, I will know what column all the Vin #'s are in, then I can use the function ExcelReadArray and pass it the column number to read, to get all the Vin #'s in that column.  

 

I guess my main question is can I use a function like ArraySearch to return the column number and the position or do I need to build a loop and use a counter. It seems like I could use ArraySearch and it return the column and position. I just don't know how to get the column number using ArraySearch. I can get the position just not the column number

Edited June 6, 2014 by RickB75

[water]

I would read row 1 into an array and then loop through the elements to search for "Vin #".

[RickB75]

Ok, this is what I've found to be the easiest and simplest way for me. Instead of using ExcelReadSheetToArray, I'm using ExcelReadArray and specifying the row number to read and the number of columns to read along with the direction (up or down). This function takes each cells data in the row and places it into its own position in the array. Using ExcelReadSheetToArray, it placed the entire row of data into a single position in the array. Now when I use ArraySearch for my string, It returns the position the string is located in (basically the column number I'm looking for).  No loops. That would be my next attempt to get the column number if this method didn't work.

Thanks for your advice / help Water.

[water]

Glad to be of service