Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a column number?
I have a large database that continues to grow on a weekly basis. Within
this database, I have formula references using vlookup, but I keep having to adjust the col_index_num for these cells because the database keeps expanding. Is there a function I can use to specify the right-most column in the database instead of using a fixed column for vlookup? Ie, "farthest-right" instead of "M", so if the database extends through columns AK or LAA or beyond it always returns the column label of that right-most data column? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a column number?
Hi
You could use a COUNTA function on a row in the database that has no empty cells (header row for instance). That count gives you the number of columns used if you start from column 1. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "sycsummit" wrote: I have a large database that continues to grow on a weekly basis. Within this database, I have formula references using vlookup, but I keep having to adjust the col_index_num for these cells because the database keeps expanding. Is there a function I can use to specify the right-most column in the database instead of using a fixed column for vlookup? Ie, "farthest-right" instead of "M", so if the database extends through columns AK or LAA or beyond it always returns the column label of that right-most data column? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a column number?
One way, check the column reference given by:
Dim lCol as long lCol = ActiveSheet.UsedRange.Columns.Count Msgbox lCol You may need to subtract one from the value returned depending on how VLookup counts, I can't remember whether it starts at zero or one...then use the variable lCol in your VLookup. Hope this helps. "sycsummit" wrote: I have a large database that continues to grow on a weekly basis. Within this database, I have formula references using vlookup, but I keep having to adjust the col_index_num for these cells because the database keeps expanding. Is there a function I can use to specify the right-most column in the database instead of using a fixed column for vlookup? Ie, "farthest-right" instead of "M", so if the database extends through columns AK or LAA or beyond it always returns the column label of that right-most data column? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to return a column number?
Awesome. Counta worked. Thanks for the tip!
"Wigi" wrote: Hi You could use a COUNTA function on a row in the database that has no empty cells (header row for instance). That count gives you the number of columns used if you start from column 1. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "sycsummit" wrote: I have a large database that continues to grow on a weekly basis. Within this database, I have formula references using vlookup, but I keep having to adjust the col_index_num for these cells because the database keeps expanding. Is there a function I can use to specify the right-most column in the database instead of using a fixed column for vlookup? Ie, "farthest-right" instead of "M", so if the database extends through columns AK or LAA or beyond it always returns the column label of that right-most data column? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return column number from column header text | Excel Discussion (Misc queries) | |||
How to return column number | Excel Programming | |||
Look up values in a column and return ALL corresponding row number | New Users to Excel | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
Excel - Return column alphabet from column number | Excel Programming |