CODEWITHSUNDEEP
excelvbainputformulainputbox
MisterBeans
MisterBeans

Reputation: 27

Using input box inside a formula

I am trying to have a user input two cells by clicking on them, then applying the cell values not the actual values into the formula so it'll work on every row until the end of the sheet. Right now the user would select D2 and G2 (these always change and are never the same two columns) and it should concatenate together as "IN-IA", then row 3 would be "IN-AL" etc.

Is there a way to get ST1 and ST2 to equal the cell and not the actual value, then fill all the way down to the end of the data?

Dim ST1 As Variant
Dim ST2 As Variant

ST1 = Application.InputBox("Select the origin state", Type:=8)
ST2 = Application.InputBox("Select the destination state", Type:=8)

ActiveCell.FormulaR1C1 = "=CONCATENATE(Trim(" & ST1 & "),""-"",Trim(" & ST2 & "))"

What it's doing: enter image description here

What I want it to do: enter image description here

Upvotes: 0

Views: 73

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You have to handle this slightly different as this is a tricky situation.

When the user presses Cancel, Application.InputBox will return False which is a Boolean value. If the user selects a range then Application.InputBox with Type:=8 will return a cell reference, as a Range object. You can't use

Dim ST1 As Variant    
ST1 = Application.InputBox("Select the origin state", Type:=8)

Because if you select a range then you need to use Set. This will create a problem when user cancels.

For similar reasons you can't even use Dim ST1 As Range and leave it at that. One way to handle this is to use On Error Resume Next

Also as @BigBen mentioned you have to use .Address property to get the address of the range.

Is this what you are trying?

Sub Sample()
    Dim ST1 As Range
    Dim ST2 As Range
    
    '~~> Pad it with OERN
    On Error Resume Next
    Set ST1 = Application.InputBox("Select the origin state", Type:=8)
    Set ST2 = Application.InputBox("Select the destination state", Type:=8)
    On Error GoTo 0
    
    '~~> Check if Application.InputBox returned a range. 
    '~~> If not then it means user pressed cancel
    If ST1 Is Nothing Or ST2 Is Nothing Then
        MsgBox "user Cancelled"
    Else
        '~~> Now you can use ST1 and ST2 as Range objects
        MsgBox "You selected: " & ST1.Address(False, False) & " and " & ST2.Address(False, False)
        '~~> ActiveCell.FormulaR1C1 = "=CONCATENATE(Trim(" & ST1 & "),""-"",Trim(" & ST2 &
    End If
End Sub

Upvotes: 1

Related Questions