CODEWITHSUNDEEP
excelvba
Studix
Studix

Reputation: 391

User defined column letter in a Range call

Asking to user the letter referencing a column ; then using the answer to get the range of this a cell with in this column at some row. But the "Range" refuses to recognize the letter input.

What am I missing in the following simple two lines ?

UserCol = Application.InputBox(" Please enter the column...", Type:=0)
Set Z = Range(UserCol & 5)

Upvotes: 0

Views: 128

Answers (4)

Chronocidal
Chronocidal

Reputation: 8081

This is a really simple bug to fix: Set Z = Range(UserCol & "5")

Why? Because when you use implicit conversion, by typing UserCol & 5, VBA includes a space between UserCol and the 5 (and also after the 5).

Here is a test:

MsgBox "A" & 5 'Outputs "A 5 "
MsgBox "A" & "5" 'Outputs "A5"

(As Gravitate points out, Application.InputBox(" Please enter the column...", Type:=0) is a Formula, so an input of "A" would give you "=""A"", and not "A" -since "=""A""5" is not a valid cell reference either, use Type:=2 for Text or InputBox(" Please enter the column...") without the Application. or the type filtering)

Upvotes: 0

Damian
Damian

Reputation: 5174

This way is easier:

Dim UserCol As String
UserCol = Application.InputBox(" Please enter the column...")
Set Z = Cells(5, UserCol)

I don't know how did you declare your UserCol or if you even declared it. If you didn't and to avoid problems always use Option Explicit on the top of your module.

Upvotes: 0

Prebsus
Prebsus

Reputation: 695

Try this:

UserCol = Application.InputBox(" Please enter the column...", Type:=2)
Set Z = Range((UserCol & 5))

I set Type to 2 to return a string from your user's input (see her for more)

Additionally, I added a parenthesis to the Range, because (UserCol & 5) becomes e.g. A5, and you need Range(A5).

Upvotes: 2

Gravitate
Gravitate

Reputation: 3062

You need to use Type:=2.

Using Type:=0 will return ="<input string>", rather than just <input string>.

So, after:

Dim UserCol As String
UserCol = Application.InputBox(" Please enter the column...", Type:=2)

You can either do:

Set Z = Cells(5, UserCol)

OR:

Set Z = Range(UserCol & "5")

I would also suggest that use Option Explicit and also fully qualify range references. e.g. instead of Set Z = Range(UserCol & "5"), use Set Z = Thisworkbook.sheets("MySheetName").Range(UserCol & "5")

Upvotes: 2

Related Questions