Trying to find column index via range name in VBA

Question

I am currently working on this spreadsheet and am running into massive difficulties. Essentially, I am trying to find the column index number that contains the range name "Average".

So, if "Average" is in column index 22, then 22 will be returned, etc.

Range("AA1").Select
Selection.Name = "Average"

Dim colNumber As Integer

colNumber = WorksheetFunction.Match("Average", ActiveWorkbook.Sheets(Sheet1).Range("1, Average"), 0)  'Average is a range name

Columns(colNumber).Insert

Each time I get different errors for this line, ranging from type mismatch to global_ failure:

colNumber = WorksheetFunction.Match("Average", ActiveWorkbook.Sheets(Sheet1).Range("1, Average"), 0)

I've searched everywhere looking for how to find a column index number based on a range name within a range, and I can't find anything. Any help is hugely appreciated.

Thank you!

jkpieterse · Accepted Answer · 2017-07-04 15:18:38Z

1

You are re-defining the range name by these lines of code:

Range("AA1").Select
Selection.Name = "Average"

In the remaining code you seem to just look for a text string "Average" in cell-content.

To get the column of the top-left corner of a range name named "Average" you would need:

Dim lCol As Long
lCol = ThisWorkbook.Names("Average").ReferstoRange.Cells(1,1).Column

answered Jul 4, 2017 at 15:18

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew Over a year ago

Ok this makes so much more sense now. The ReferstoRange method was the missing key I think. Thanks Jkpieterse!

Collectives™ on Stack Overflow

Trying to find column index via range name in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related