Range dependent on InputBox

[288enzo]

Is it possible to set a cell range based on an inputbox?

As an example I have an inputbox, and based on the users response I would like to set a particular range that can be used throughout my code.

In my mind it would be something like
Select Case strArea
Case 80
x="E12"
Case 82
x="E13"
Case 84
x="E17"

From there I can use Range(x) and based on the above Range("E12") would be used if 80 was entered.

Am I out of my mind?

Thank you

 

[steve the fish]

Yes you could do that or maybe directly from the inputbox eg:

Dim rng As Range
Set rng = Application.InputBox("Enter cell reference", , , , , , , 8)

or maybe this:

Select Case strArea
    Case 80
        Set x = Range("E12")
    Case 82
        Set x = Range("E13")
    Case 84
        Set x = Range("E17")
End Select

or like you said:

Select Case strArea
    Case 80
        x = "E12"
    Case 82
        x = "E13"
    Case 84
        x = "E17"
End Select

Set rng = Range(x)

 

[288enzo]

Yes you could do that or maybe directly from the inputbox eg:

Dim rng As Range
Set rng = Application.InputBox("Enter cell reference", , , , , , , 8)

or maybe this:

Select Case strArea
    Case 80
        Set x = Range("E12")
    Case 82
        Set x = Range("E13")
    Case 84
        Set x = Range("E17")
End Select

or like you said:

Select Case strArea
    Case 80
        x = "E12"
    Case 82
        x = "E13"
    Case 84
        x = "E17"
End Select

Set rng = Range(x)

Silly me, I thought it would be that easy.
I'm getting an error "Type mismatch" with a test.

Select Case strArea
    Case 87
        x = "A1"
End Select
Range(x) = "Hello, this worked"

 

[steve the fish]

You will need to give me exactly what you are running when you get the error including the Dim statements. The below will run and enter "hello" in the appropriate cell of the activesheet if you enter one of the numbers present in the case select.

Sub hello()

Dim strArea As String, x As String

strArea = InputBox("Enter Number")

Select Case strArea
    Case 80
        x = "E12"
    Case 82
        x = "E13"
    Case 84
        x = "E17"
End Select

If Len x > 0 Then Range(x) = "hello"
    
End Sub

 

[288enzo]

You will need to give me exactly what you are running when you get the error including the Dim statements. The below will run and enter "hello" in the appropriate cell of the activesheet if you enter one of the numbers present in the case select.

Sub hello()

Dim strArea As String, x As String

strArea = InputBox("Enter Number")

Select Case strArea
    Case 80
        x = "E12"
    Case 82
        x = "E13"
    Case 84
        x = "E17"
End Select

If Len x > 0 Then Range(x) = "hello"
   
End Sub

Thank you, it was "x As String" I was missing.

Is "If Len X > 0 Then" used in the event they don't enter a number?
I ran the code without it and it worked perfectly.

Thank you again

 

[steve the fish]

Its in case you enter something that isnt in your list of possibilites. It prevents error. Try putting 100 in the inputbox without it.

 

[288enzo]

You will need to give me exactly what you are running when you get the error including the Dim statements. The below will run and enter "hello" in the appropriate cell of the activesheet if you enter one of the numbers present in the case select.

Sub hello()

Dim strArea As String, x As String

strArea = InputBox("Enter Number")

Select Case strArea
    Case 80
        x = "E12"
    Case 82
        x = "E13"
    Case 84
        x = "E17"
End Select

If Len x > 0 Then Range(x) = "hello"
   
End Sub

How would I go about using it with this if I wanted C13 in the formula to change depending on what was entered for x?

.Formula = "=""Area "" & LEFT(C13,3)"

Would I do it without the quotes?

x = E17

 

[288enzo]

Its in case you enter something that isnt in your list of possibilites. It prevents error. Try putting 100 in the inputbox without it.

I don't have a list of what can be entered.

This is my input box

strArea = InputBox("Area? (80,82,83,84,87)")

I'm only giving a suggestion as to what should be entered

 

[steve the fish]

Yes you do. Its in the code. Case 80 etc etc.

 

[288enzo]

Yes you do. Its in the code. Case 80 etc etc.

Interesting, thank you for that nugget