Dim As Range Object

[Billy Leung]

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

 

[ozgrid.com]

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

 

[Billy Leung]

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]

Why not clear the Range before passing the Range Object?

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

 

[Billy Leung]

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]

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

 

[Billy Leung]

Dave,
Thanks.
Billy

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