Why do I need an "& 1" here?

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
Why does this code require the "& 1" in the conversion from column letter to number?

Code:
Dim ColNum As Long
Dim ColLet As String


ColLet = InputBox("What is the column letter of the column you want to analyze?")
  
ColNum = Range(ColLet & 1).Column


MsgBox ColNum

If I remove the & 1 I get an error.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
A range represents a particular cell or group of cells. Without the & 1 you do not have a legitimate range. So if in the input box, a user responded "C", then the range would be "C1" which is a legitimate range to work with.
 
Upvote 0
So that Range(ColLet & 1) produces a valid range ref (like A1, B1,etc.)
 
Upvote 0
Of course! Thank you.

I can change it to something like
Code:
ColNum = Range(ColLet & ":" & ColLet).Column

That is longer, but I get it.

My instinct confused me, because I want to type it out like this:


Code:
[COLOR=#333333]ColNum = Range(ColLet & "1").Column[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top