Dim As Range Object

  • Thread starter Thread starter Billy Leung
  • Start date Start date
B

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
 
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
 
Why not clear the Range before passing the Range Object?

Or use;
Set PasteAt =Cells(Rows.Count,"A").End(XlUp)(2,1)
 
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
 
IF PasteAt<>VbNullString Then Range("A4").Cut Cells(Rows.Count,
Columns.Count)
'YOUR CODE
 
Back
Top