How to return a column number?

[sycsummit]

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?

 

[Wigi]

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.

 

[XP]

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]

Awesome. Counta worked. Thanks for the tip!

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.