Excel VBA problem on application.inputbox that returns range

[Ambichamti]

This code puts formula in specified range in excel by asking users for formula input by using input box and also for range to put formula in excel by using input box that returns range.
This works fine if we specify range and formula but as if we use cancel it delivers false it gave an error I did a web search for help.
I got a solution that is using result of range input box in variant variable and then using "if type of v as range" as in code.
But v returns empty so I can't set Formulacell range variable as v variant.so this code fails as it exit's sub as in code.
Please give a solution even instead of using variant variable

sub WoVbaAppInputbox

Dim Myformula as string
Dim Formulacell as range
Dim v as variant

Myformula=Application.inputbox(prompt:="Enter formula", _Default:="=SUM(", _Type:=0)

V = Application.inputbox(prompt:="specify range", _Type:=8)

If Not type of v is Range then
Exit sub
Else
Set Formulacell = v
End if

Formulacell.formulalocal= Myformula

End sub

[BIFanatic]

Type 8 returns a range reference so you need to use "Set V =" I added a bit of error handler too.




Option Explicit


Sub WoVbaAppInputbox()


Dim Myformula As String
Dim Formulacell As Range
Dim v As Range
Dim TryAgain As VbMsgBoxResult

Myformula = Application.InputBox(prompt:="Enter formula", Default:="=SUM(", Type:=0)
If Myformula = "False" Then Exit Sub

SelectAgain:
On Error Resume Next
Set v = Application.InputBox(prompt:="specify range", Type:=8)


If Err.Number = 13 Then
TryAgain = MsgBox("you didn't select a range, do you wish to select again?", vbYesNo + vbQuestion)
If TryAgain = vbYes Then
GoTo SelectAgain
Else
MsgBox "You do not wish to continue the code will now exit", vbInformation
Exit Sub
End If
End If


v.Formula = Myformula


End Sub

[Ambichamti]

But the code I found in stackoverflow uses v as variant and when asked they seemed to be correct they also explained
Here is link
https://stackoverflow.com/questions/19609479/handle-cancellation-of-inputbox-to-select-range/62550211#62550211

[BIFanatic]

There are multiple ways to do the same thing, I prefer using range, because most of the time I need the help of intellisense and have to use the variable in different part of the code, although I could set my range variable equal to v later, but I prefer to operate differently.

[mikerickson]

I like this formulation


Dim uiRange as Range

On Error Resume Next
Set uiRange = Application.InputBox("select range", type:=8)
On Error Goto O

If uiRange Is Nothing then Exit Sub: Rem cancel pressed

Set FormulaCell = uiRange