Dialogue box to select range for autofill macro

**judasdac** · 04-30-2009, 01:22 PM

Hi all,
I have an autofill macro that copies a formula in one cell to all the cells in a defined range.

Sub autofill()
'autofills a formula through range
'

Range("m3961").autofill Destination:=Range("m3961:m7921"), Type:=xlFillDefault

End Sub

Currently, I manually edit the macro each time I'm using a unqiue range, but I feel like there is a more efficient way to do this. Any ideas? Is it possible to create a dialogue box that allow me to select the range?

Thanks in advance for the help.

Cheers!

**royUK** · 04-30-2009, 01:53 PM

Something like this

    Dim rFill  As Range
    On Error Resume Next
    Application.DisplayAlerts = False
    Set rFill = Application.InputBox(Prompt:= _
                                     "Please select a range with your Mouse to be bolded.", _
                                     Title:="SELECT RANGE", Type:=8)
    On Error GoTo 0
    Application.DisplayAlerts = True
    If rRange Is Nothing Then
        Exit Sub
    Else:     Range("m3961").autofill Destination:=rfill, Type:=xlFillDefault
    End If

**judasdac** · 04-30-2009, 03:07 PM

Roy --- this is great! However, I'm still struggling a bit. When I went to run the macro, I received a

'424' run-time object required

This is the code:

Sub autofill()
'autofills a formula through range
'

   Dim rFill  As Range
    On Error Resume Next
    Application.DisplayAlerts = False
    Set rFill = Application.InputBox(Prompt:= _
                                     "Please select a range with your Mouse to be bolded.", _
                                     Title:="SELECT RANGE", Type:=8)
    On Error GoTo 0
    Application.DisplayAlerts = True
    If rRange Is Nothing Then
        Exit Sub
    Else:     Range("a2").autofill Destination:=rFill, Type:=xlFillDefault
    End If
End Sub

I'm sure I did something to make a bit of a hash of it, but I'm not sure what. Any ideas?

**royUK** · 05-01-2009, 03:06 AM

Try keeping your original macro as is, I would change the name from autofill() to myautofill() to avoid possible problems, then

add this macro to call it

 sub getRange()

Dim rFill  As Range
    On Error Resume Next
    Application.DisplayAlerts = False
    Set rFill = Application.InputBox(Prompt:= _
                                     "Please select a range with your Mouse to be bolded.", _
                                     Title:="SELECT RANGE", Type:=8)
    On Error GoTo 0
    Application.DisplayAlerts = True
    If rRange Is Nothing Then
        Exit Sub
    Else: myautofill
    End If
End Sub

Thread: Dialogue box to select range for autofill macro

Thread Tools

Display

Dialogue box to select range for autofill macro

Re: Dialogue box to select range for autofill macro

Re: Dialogue box to select range for autofill macro

Re: Dialogue box to select range for autofill macro

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions