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]

As for the other you cant use Range(x) then use Left(x,3). That doesnt make sense. You are overwriting the cell so how can you have the leftmost 3 characters also. Unless you can explain what you want.

 

[288enzo]

As for the other you cant use Range(x) then use Left(x,3). That doesnt make sense. You are overwriting the cell so how can you have the leftmost 3 characters also. Unless you can explain what you want.

In this example, if 82 is inputted then in cell E13 the formula would be ="Area " & LEFT(C13,3)

    Dim strArea As String, y As String
    Select Case strArea
        Case 82
            y = "E13"
        Case 83, 84
            y = "E17"
    End Select
    Select Case strArea
        Case 82, 83, 84
            Rows("4").Delete
            With Worksheets("Dealer Status").Range(y)
            .Formula = "=""Area "" & LEFT(C13,3)"
            .HorizontalAlignment = xlLeft
            End With
End Select

What I am trying to accomplish is making the C13 as part of the above formula be dynamic.

What I was thinking was this, but having to do it without the quotes because there are quotes in the formula =LEFT(C13,3)

    Dim strArea As String, y As String, z As String
    Select Case strArea
        Case 82
            y = "E13"
            z = E13
        Case 83, 84
            y = "E17"
    End Select
    Select Case strArea
        Case 82, 83, 84
            Rows("4").Delete
            With Worksheets("Dealer Status").Range(y)
            .Formula = "=""Area "" & LEFT(z,3)"
            .HorizontalAlignment = xlLeft
            End With
End Select

But that didn't work

 

[288enzo]

In this example, if 82 is inputted then in cell E13 the formula would be ="Area " & LEFT(C13,3)

    Dim strArea As String, y As String
    Select Case strArea
        Case 82
            y = "E13"
        Case 83, 84
            y = "E17"
    End Select
    Select Case strArea
        Case 82, 83, 84
            Rows("4").Delete
            With Worksheets("Dealer Status").Range(y)
            .Formula = "=""Area "" & LEFT(C13,3)"
            .HorizontalAlignment = xlLeft
            End With
End Select

What I am trying to accomplish is making the C13 as part of the above formula be dynamic.

What I was thinking was this, but having to do it without the quotes because there are quotes in the formula =LEFT(C13,3)

    Dim strArea As String, y As String, z As String
    Select Case strArea
        Case 82
            y = "E13"
            z = E13
        Case 83, 84
            y = "E17"
    End Select
    Select Case strArea
        Case 82, 83, 84
            Rows("4").Delete
            With Worksheets("Dealer Status").Range(y)
            .Formula = "=""Area "" & LEFT(z,3)"
            .HorizontalAlignment = xlLeft
            End With
End Select

But that didn't work

I figured it out

    Dim strArea As String
    Select Case strArea
        Case 82
            y = "E13"
            z = "E12"
        Case 83, 84
            y = "E17"
    End Select
    Select Case strArea
        Case 82, 83, 84
            Rows("4").Delete
            With Worksheets("Dealer Status").Range(y)
            .Formula = "=""Area "" & LEFT(" & z & ",3)"
            .HorizontalAlignment = xlLeft
            End With
End Select