Excel VBA - Selecting Columns dynamically using Column headers

Question

I am trying to select a column dynamically based on the Column headers in VBA. This is the code that I am using ,

Sub trial()

Dim colm As Long
Dim nwb As Workbook, wb As Workbook
Dim nwk As Worksheet, wk As Worksheet, wk1 As Worksheet

co1m = WorksheetFunction.Match("Header", Sheets("Sheet1").Rows(1), 0)

Columns(colm).Select

End Sub

when I try to debug it, it shows the value of 5 in colm and at the same time on the next line columns(colm).select, it shows 0 in the place of colm. Kindly help me with where I am going wrong in this. Thanks in advance

R3uK · Accepted Answer · 2017-03-01 14:06:42Z

1

You have a typo in there! You have co1m and colm.

Using Option Explicit would have throw a compile error saying that co1m wasn't declared! ;)

Option Explicit

Sub trial()

Dim colm As Long
Dim nwb As Workbook, wb As Workbook
Dim nwk As Worksheet, wk As Worksheet, wk1 As Worksheet

colm = WorksheetFunction.Match("Header", Sheets("Sheet1").Rows(1), 0)

Columns(colm).Select

End Sub

answered Mar 1, 2017 at 14:06

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

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

4 Comments

Student of the Digital World Over a year ago

Thanks @R3uK. I am not sure its the typo that I had in the code because I looked in the code it wasn't a typo but when I added this Option Explicit, it just started working. Is there any reason behind this Option Explicit? Thanks again buddy !!

R3uK Over a year ago

@VBA_Begineer : Option Explicit is just a best practice (you can set VBE to use it by default, check in the options) to avoid that kind of error, it'll force you to declare all the variable there is in your code, and so it'll warn you when you have a typo! ;) Please accept the answer as this solved your issue.

Student of the Digital World Over a year ago

@R3_uK Thanks mate. I was made to wait for 10 minutes by Stack Overflow to accept your answer. Sorry for that. Thanks again !!

R3uK Over a year ago

@VBA_Begineer : Ahaha NP, I forgot about that limitation! ;)

Collectives™ on Stack Overflow

Excel VBA - Selecting Columns dynamically using Column headers

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related