Hi,

I am writing a macro to call another sub. One of the argument that I pass to
the other sub is a range object. I just found that if the range has content,
value of the variable will become to the value of the range. In my case, if
the range("A4") store a string "ABC", then the variable PasteAt="ABC". This
will result in error when the other sub is going to paste data of recordset
at a specific range. Can you tell me how to solve this problem? Your help
will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String, PasteAt As
Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
....
....
Call GetData(Server, Database, SQLcommand, PasteAt)
....
....
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As Range)
....
....
End Sub


Billy

What value should your range Object store if NOT the Cell you Set it to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I pass
to the other sub is a range object. I just found that if the range has
content, value of the variable will become to the value of the range. In
my case, if the range("A4") store a string "ABC", then the variable
PasteAt="ABC". This will result in error when the other sub is going to
paste data of recordset at a specific range. Can you tell me how to solve
this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String, PasteAt As
Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As Range)
...
...
End Sub


Billy

Hi Dave,
Thanks for your prompt response. In my case, I want to pass the "location"
to the other sub. The "location" means where I would like to paste the
recordset. If the range("A4") is empty, the macro run perfectly. (By the
way, if I type ?PasteAt in immediate windows, it doesn't show anything. Is
that normal?). Should I have to clear the content of range("A4") before I
set PasteAT?

Billy

"ozgrid.com" wrote in message
...
What value should your range Object store if NOT the Cell you Set it to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I pass
to the other sub is a range object. I just found that if the range has
content, value of the variable will become to the value of the range. In
my case, if the range("A4") store a string "ABC", then the variable
PasteAt="ABC". This will result in error when the other sub is going to
paste data of recordset at a specific range. Can you tell me how to solve
this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String, PasteAt
As Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As Range)
...
...
End Sub


Billy

Why not clear the Range before passing the Range Object?

Or use;
Set PasteAt =Cells(Rows.Count,"A").End(XlUp)(2,1)



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
Thanks for your prompt response. In my case, I want to pass the "location"
to the other sub. The "location" means where I would like to paste the
recordset. If the range("A4") is empty, the macro run perfectly. (By the
way, if I type ?PasteAt in immediate windows, it doesn't show anything. Is
that normal?). Should I have to clear the content of range("A4") before I
set PasteAT?

Billy

"ozgrid.com" wrote in message
...
What value should your range Object store if NOT the Cell you Set it to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I
pass to the other sub is a range object. I just found that if the range
has content, value of the variable will become to the value of the
range. In my case, if the range("A4") store a string "ABC", then the
variable PasteAt="ABC". This will result in error when the other sub is
going to paste data of recordset at a specific range. Can you tell me
how to solve this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String, PasteAt
As Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As
Range)
...
...
End Sub


Billy

Hi Dave,
In my case, yes, I can clear the range first. But for arguement sake, if I
need to maintain the value but need to pass the range object, what should I
do?

(the second option you offer is going to the last cell of the sheet. am I
correct?)

Billy

"ozgrid.com" wrote in message
...
Why not clear the Range before passing the Range Object?

Or use;
Set PasteAt =Cells(Rows.Count,"A").End(XlUp)(2,1)



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
Thanks for your prompt response. In my case, I want to pass the
"location" to the other sub. The "location" means where I would like to
paste the recordset. If the range("A4") is empty, the macro run
perfectly. (By the way, if I type ?PasteAt in immediate windows, it
doesn't show anything. Is that normal?). Should I have to clear the
content of range("A4") before I set PasteAT?

Billy

"ozgrid.com" wrote in message
...
What value should your range Object store if NOT the Cell you Set it to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I
pass to the other sub is a range object. I just found that if the range
has content, value of the variable will become to the value of the
range. In my case, if the range("A4") store a string "ABC", then the
variable PasteAt="ABC". This will result in error when the other sub is
going to paste data of recordset at a specific range. Can you tell me
how to solve this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String, PasteAt
As Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As
Range)
...
...
End Sub


Billy

IF PasteAt<VbNullString Then Range("A4").Cut Cells(Rows.Count,
Columns.Count)
'YOUR CODE



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
In my case, yes, I can clear the range first. But for arguement sake, if I
need to maintain the value but need to pass the range object, what should
I do?

(the second option you offer is going to the last cell of the sheet. am I
correct?)

Billy

"ozgrid.com" wrote in message
...
Why not clear the Range before passing the Range Object?

Or use;
Set PasteAt =Cells(Rows.Count,"A").End(XlUp)(2,1)



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
Thanks for your prompt response. In my case, I want to pass the
"location" to the other sub. The "location" means where I would like to
paste the recordset. If the range("A4") is empty, the macro run
perfectly. (By the way, if I type ?PasteAt in immediate windows, it
doesn't show anything. Is that normal?). Should I have to clear the
content of range("A4") before I set PasteAT?

Billy

"ozgrid.com" wrote in message
...
What value should your range Object store if NOT the Cell you Set it
to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I
pass to the other sub is a range object. I just found that if the
range has content, value of the variable will become to the value of
the range. In my case, if the range("A4") store a string "ABC", then
the variable PasteAt="ABC". This will result in error when the other
sub is going to paste data of recordset at a specific range. Can you
tell me how to solve this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String,
PasteAt As Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As
Range)
...
...
End Sub


Billy

Dave,
Thanks.
Billy
"ozgrid.com" wrote in message
...
IF PasteAt<VbNullString Then Range("A4").Cut Cells(Rows.Count,
Columns.Count)
'YOUR CODE



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
In my case, yes, I can clear the range first. But for arguement sake, if
I need to maintain the value but need to pass the range object, what
should I do?

(the second option you offer is going to the last cell of the sheet. am I
correct?)

Billy

"ozgrid.com" wrote in message
...
Why not clear the Range before passing the Range Object?

Or use;
Set PasteAt =Cells(Rows.Count,"A").End(XlUp)(2,1)



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi Dave,
Thanks for your prompt response. In my case, I want to pass the
"location" to the other sub. The "location" means where I would like to
paste the recordset. If the range("A4") is empty, the macro run
perfectly. (By the way, if I type ?PasteAt in immediate windows, it
doesn't show anything. Is that normal?). Should I have to clear the
content of range("A4") before I set PasteAT?

Billy

"ozgrid.com" wrote in message
...
What value should your range Object store if NOT the Cell you Set it
to?



--
Regards
Dave Hawley
www.ozgrid.com
"Billy Leung" wrote in message
...
Hi,

I am writing a macro to call another sub. One of the argument that I
pass to the other sub is a range object. I just found that if the
range has content, value of the variable will become to the value of
the range. In my case, if the range("A4") store a string "ABC", then
the variable PasteAt="ABC". This will result in error when the other
sub is going to paste data of recordset at a specific range. Can you
tell me how to solve this problem? Your help will be appreciated.

The following is my coding.


Sub main()
Dim Server As String, Database As String, SQLcommand As String,
PasteAt As Range
Server = "SBS-2003"
Database = "Data-03"
Set PasteAt = Range("A4")
...
...
Call GetData(Server, Database, SQLcommand, PasteAt)
...
...
End Sub

Sub GetData(Svr As String, Db As String, SqlCmd As String, Pste As
Range)
...
...
End Sub


Billy