named range in macro call
 

I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). Even if I have to use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!

named range in macro call
 

Two options:

Private Sub CommandButton1_Click()
Call macro1(Range("NamedRange1"))
End Sub

or

Private Sub CommandButton2_Click()
Dim myRng As Range
Set myRng = Range("NamedRange2")
Call macro1(myRng)
End Sub

Regards,
Per

On 1 Apr., 21:46, hjc wrote:
I have written a macro that I would like to have operate the same way on
different data ranges. *I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
* * Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
* * Call Macro1( "NamedRange2" )
End Sub

and so on. *However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). *Even if I have to use
cell references instead of a named range, I could live with that. *Does
anybody know if there is a way to do this?

Thanks!

named range in macro call
 

The one thing you didn't show us that we needed to see is your macro
(actually, since it receives an argument, it is a subroutine and not a
macro). See if this minimal structured relationship helps you any...

Private Sub CommandButton2_Click()
Call Macro1("NamedRange2")
End Sub

Sub Macro1(RngName As String)
MsgBox Range(RngName).Address
End Sub

--
Rick (MVP - Excel)



"hjc" wrote in message
...
I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that
each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying
a
range name in the call to the macro (if there is one). Even if I have to
use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!

named range in macro call
 

Same macro to work on different ranges?

Private Sub CommandButton1_Click()
Application.Goto Reference:="NamedRange1"
Call Macro1
End Sub

Private Sub CommandButton1_Click()
Application.Goto Reference:="NamedRange2"
Call Macro1
End Sub


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 12:46:05 -0700, hjc
wrote:

I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). Even if I have to use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!