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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What value should your range Object store if NOT the Cell you Set it to?
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Billy Leung" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> What value should your range Object store if NOT the Cell you Set it to?
>>
>>
>>
>> --
>> Regards
>> Dave Hawley
>> www.ozgrid.com
>> "Billy Leung" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
news:%2371I2%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> What value should your range Object store if NOT the Cell you Set it to?
>>>
>>>
>>>
>>> --
>>> Regards
>>> Dave Hawley
>>> www.ozgrid.com
>>> "Billy Leung" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%2371I2%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> What value should your range Object store if NOT the Cell you Set it
>>>> to?
>>>>
>>>>
>>>>
>>>> --
>>>> Regards
>>>> Dave Hawley
>>>> www.ozgrid.com
>>>> "Billy Leung" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> IF PasteAt<>VbNullString Then Range("A4").Cut Cells(Rows.Count,
> Columns.Count)
> 'YOUR CODE
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Billy Leung" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%2371I2%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> What value should your range Object store if NOT the Cell you Set it
>>>>> to?
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards
>>>>> Dave Hawley
>>>>> www.ozgrid.com
>>>>> "Billy Leung" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>