using application.inputbox

[jimrward]

in simple terms I want to achieve the following using INPUTBOX

workbook has 2 sheets, sheet1 and sheet2

I wish to select a cell in sheet2, run a macro to prompt for a source cell, use the address of the source cell to create a simple reference

for example I select sheet2!A6 and I wish to set the formula to =sheet1!A3, where Sheet1!A3 is prompted for using INPUTBOX(...type=8)

I keep getting various object errors, this is the current state of play

Sub TestInputBox2()
    Dim myRange As Range
    Set myRange = Application.InputBox(Prompt:= _
        "Please Select a Range", _
        Title:="InputBox Method", Type:=8)
 
    If myRange Is Nothing Then
        ' Range is blank
    Else
        ActiveCell.Formula = "=" & myRange
    End If
End Sub

 

[GlennUK]

Shouldn't that be:

ActiveCell.Formula = "=" & myRange.Address

... but, shouldn't you also check that only a single cell has been selected as the range?

 

[Misca]

Try:

Sub TestInputBox2()
    Dim myRange As Range
 
    On Error Resume Next    'Skips the error if myRange is nothing
 
    Set myRange = Application.InputBox(Prompt:= _
        "Please Select a Range", _
        Title:="InputBox Method", Type:=8)
 
    If myRange Is Nothing Then
        ' Range is blank
    Else
        ActiveCell.Formula = "=" & myRange.Address
        'ActiveCell.Formula = "=" & Replace(myRange.Address, "$", "")
    End If
End Sub

And if you want the sheet name to be included use:

ActiveCell.Formula = "='" & myRange.Parent.Name & "'!" & myRange.Address

 

[gingersteve]

Sub TestInputBox2()
Dim myRange As Range
Set myRange = Application.InputBox(Prompt:= _
"Please Select a Range", _
Title:="InputBox Method", Type:=8)

If myRange Is Nothing Then
' Range is blank
Else
ActiveCell.Formula = "='" & myRange.Worksheet.Name & "'!" & myRange.Address
End If
End Sub


Works for me

 

[jimrward]

thanks all

it was the myrange.parent.name that I was missing there was me thinking address meant the complete address, ho hum.

I had tried myrange.address, but in debug mode it was showing the contents of the cell and when patched into the recipient cell was showing =$A$4 as the formula and 0 as the value as it was taking it from the current sheet

all working fine now ta