I am obviously missing something since I can't get this to work. I am
building a subroutine that will work on several worksheets. The Sub would be
initiated by clicking a button on the worksheet. To make it universal, I
need to pass the Worksheet name, the Range to be manipulated, a Range on
another worksheet which holds some data, and a constant. Here is what I have
tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As Long
WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As Range, RCol
As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do to fix
this? Thanks in advance for your help!

Ray

Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
I am obviously missing something since I can't get this to work. I am
building a subroutine that will work on several worksheets. The Sub
would be initiated by clicking a button on the worksheet. To make it
universal, I need to pass the Worksheet name, the Range to be
manipulated, a Range on another worksheet which holds some data, and
a constant. Here is what I have tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As Range,
RCol As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do to
fix this? Thanks in advance for your help!

Ray

H Frank,

Thanks for your reply. I always have Option Explicit Set, however, the code
is running on another machine so I retyped it. My 2 thumbs and their 8
friends messed up. Both ranges exist before calling the macro, and there is
data in them. I added in the Set commands and stepped through the macro.
When I try to execute the Set ANRange = Range("RangeName1") code I get the
same error.

Any more suggestions? Thanks again!

Ray

Frank Kabel wrote in message
...
Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
I am obviously missing something since I can't get this to work. I am
building a subroutine that will work on several worksheets. The Sub
would be initiated by clicking a button on the worksheet. To make it
universal, I need to pass the Worksheet name, the Range to be
manipulated, a Range on another worksheet which holds some data, and
a constant. Here is what I have tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As Range,
RCol As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do to
fix this? Thanks in advance for your help!

Ray

Hi Ray
I just tested it again and if I have a Rangename1 as defined name in my
active workbook/sheet it works. Could you please post again the code
you tried after the corrections :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
H Frank,

Thanks for your reply. I always have Option Explicit Set, however,
the code is running on another machine so I retyped it. My 2 thumbs
and their 8 friends messed up. Both ranges exist before calling the
macro, and there is data in them. I added in the Set commands and
stepped through the macro. When I try to execute the Set ANRange =
Range("RangeName1") code I get the same error.

Any more suggestions? Thanks again!

Ray

Frank Kabel wrote in message
...
Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total
change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
I am obviously missing something since I can't get this to work. I
am building a subroutine that will work on several worksheets. The
Sub would be initiated by clicking a button on the worksheet. To
make it universal, I need to pass the Worksheet name, the Range to
be manipulated, a Range on another worksheet which holds some data,
and a constant. Here is what I have tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol
As
Long WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As
Range, RCol As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do
to fix this? Thanks in advance for your help!

Ray

Assume RangeName1 (and RangeName2) is a defined name. If it does not refer
to the sheet containing the code, then you need to qualifiy it with the
worksheet name. Normally this is not required, but when used in a sheet
module, it is.

assume
Name: RangeName1
Refersto: =Sheet3!$A$1:$A$100

and code is in Sheet2 code module. Then

Set ANRange = Worksheets("Sheet3").Range("RangeName1")

--

Regards,
Tom Ogilvy

"Ray Batig" wrote in message
nk.net...
H Frank,

Thanks for your reply. I always have Option Explicit Set, however, the
code
is running on another machine so I retyped it. My 2 thumbs and their 8
friends messed up. Both ranges exist before calling the macro, and there
is
data in them. I added in the Set commands and stepped through the macro.
When I try to execute the Set ANRange = Range("RangeName1") code I get the
same error.

Any more suggestions? Thanks again!

Ray

Frank Kabel wrote in message
...
Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
I am obviously missing something since I can't get this to work. I am
building a subroutine that will work on several worksheets. The Sub
would be initiated by clicking a button on the worksheet. To make it
universal, I need to pass the Worksheet name, the Range to be
manipulated, a Range on another worksheet which holds some data, and
a constant. Here is what I have tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As Range,
RCol As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do to
fix this? Thanks in advance for your help!

Ray

Thanks to all who commented. The addition of the Worksheets("Sheet3").
specifier did it.

Ray
Tom Ogilvy wrote in message
...
Assume RangeName1 (and RangeName2) is a defined name. If it does not
refer
to the sheet containing the code, then you need to qualifiy it with the
worksheet name. Normally this is not required, but when used in a sheet
module, it is.

assume
Name: RangeName1
Refersto: =Sheet3!$A$1:$A$100

and code is in Sheet2 code module. Then

Set ANRange = Worksheets("Sheet3").Range("RangeName1")

--

Regards,
Tom Ogilvy

"Ray Batig" wrote in message
nk.net...
H Frank,

Thanks for your reply. I always have Option Explicit Set, however, the
code
is running on another machine so I retyped it. My 2 thumbs and their 8
friends messed up. Both ranges exist before calling the macro, and there
is
data in them. I added in the Set commands and stepped through the macro.
When I try to execute the Set ANRange = Range("RangeName1") code I get
the
same error.

Any more suggestions? Thanks again!

Ray

Frank Kabel wrote in message
...
Hi
some remarks:
- first you should add the line 'Option explicit' to your module as
there is a typo in the line
Long WkShts = "SheetName"
should read
Long WkSht = "SheetName"

For your error: Is RangeName1 a defined name?. If not it won't work.
Also you have to use Set to assign a range object. So in total change
your code to
Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long
WkSht = "SheetName"
Set ANRange = Range("RangeName1")
Set XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Ray Batig wrote:
I am obviously missing something since I can't get this to work. I
am
building a subroutine that will work on several worksheets. The Sub
would be initiated by clicking a button on the worksheet. To make it
universal, I need to pass the Worksheet name, the Range to be
manipulated, a Range on another worksheet which holds some data, and
a constant. Here is what I have tried.

On the Worksheet:

Private Sub CommandButton2_Click()
Dim WkSht As String, ANRange As Range, XlSlots As Range, RCol As
Long WkShts = "SheetName"
ANRange = Range("RangeName1")
XlSlots = Range("RangeName2")
RCol = 5
Call BuildReport( WkSht, ANRange, XlSlots, RCol)
End Sub

In Module:
Sub BuildReport( WkSht As String, ANRange As Range, XlSlots As
Range,
RCol As Long)

Code
End Sub

Running this code produces a Run-time '1004' Application-defined or
object-defined error.

I have tried several other versions without success. What can I do
to
fix this? Thanks in advance for your help!

Ray