to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

Maybe these two subs can illustrate the difference a little better. Create a
new workbook, insert a code module into the VBE and copy these two snippets
into the code module. Then run the snippets to see how the same variable
'word' takes on different characteristics. In one case, it acts as an object
which contains a string property, and in the other it is a variable equal to
a string value.

Sub objTest()
Dim str As Range
Set str = ActiveWorkbook.Sheets(1).Cells(3, 3)
str = "Hello"
End Sub

Sub strValTest()
Dim str As String
ActiveWorkbook.Sheets(1).Cells(3, 3) = "Hello"
str = ActiveWorkbook.Sheets(1).Cells(3, 3).Value
MsgBox str
End Sub

"OlieH" wrote:

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

When I tried to runthe first example (excel 2003) the set command
gave me a 438 error; Object doesn't support this property or method.
What am I doing wrong?
Thanks for your continued help.

"JLGWhiz" wrote:

Maybe these two subs can illustrate the difference a little better. Create a
new workbook, insert a code module into the VBE and copy these two snippets
into the code module. Then run the snippets to see how the same variable
'word' takes on different characteristics. In one case, it acts as an object
which contains a string property, and in the other it is a variable equal to
a string value.

Sub objTest()
Dim str As Range
Set str = ActiveWorkbook.Sheets(1).Cells(3, 3)
str = "Hello"
End Sub

Sub strValTest()
Dim str As String
ActiveWorkbook.Sheets(1).Cells(3, 3) = "Hello"
str = ActiveWorkbook.Sheets(1).Cells(3, 3).Value
MsgBox str
End Sub

"OlieH" wrote:

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

Did you copy the code that I gave you or do some modification to add your
range references in? Both snippets performed as expected on my xl2003. The
first one put "Hello" in cell C3 and the second one displayed a message box
with "Hello"

The point is, that one of them uses the variable str as a range object,
where the other one uses the variable as a string data type. When you want
to use a variable as a range object, you have to use the Set keyword and
then make the variable equal to a range, which can inlude the workbook,
sheet and cell, but must be the cell reference as a minimum. When you want
to use the variable as a string, date, number or anything other than an
object, you do not use the Set key word, but you use the Value property of
the object for the variable value. If you look at the code snippets, you
will see that where Set is used, Value is not and in the other it is vice
versa.


"OlieH" wrote in message
...
When I tried to runthe first example (excel 2003) the set command
gave me a 438 error; Object doesn't support this property or method.
What am I doing wrong?
Thanks for your continued help.

"JLGWhiz" wrote:

Maybe these two subs can illustrate the difference a little better.
Create a
new workbook, insert a code module into the VBE and copy these two
snippets
into the code module. Then run the snippets to see how the same variable
'word' takes on different characteristics. In one case, it acts as an
object
which contains a string property, and in the other it is a variable equal
to
a string value.

Sub objTest()
Dim str As Range
Set str = ActiveWorkbook.Sheets(1).Cells(3, 3)
str = "Hello"
End Sub

Sub strValTest()
Dim str As String
ActiveWorkbook.Sheets(1).Cells(3, 3) = "Hello"
str = ActiveWorkbook.Sheets(1).Cells(3, 3).Value
MsgBox str
End Sub

"OlieH" wrote:

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in
that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

The code performed exactly as you stated. I am not sure why
the code did not work for the first time, but I thnk you very much.

"JLGWhiz" wrote:

Did you copy the code that I gave you or do some modification to add your
range references in? Both snippets performed as expected on my xl2003. The
first one put "Hello" in cell C3 and the second one displayed a message box
with "Hello"

The point is, that one of them uses the variable str as a range object,
where the other one uses the variable as a string data type. When you want
to use a variable as a range object, you have to use the Set keyword and
then make the variable equal to a range, which can inlude the workbook,
sheet and cell, but must be the cell reference as a minimum. When you want
to use the variable as a string, date, number or anything other than an
object, you do not use the Set key word, but you use the Value property of
the object for the variable value. If you look at the code snippets, you
will see that where Set is used, Value is not and in the other it is vice
versa.


"OlieH" wrote in message
...
When I tried to runthe first example (excel 2003) the set command
gave me a 438 error; Object doesn't support this property or method.
What am I doing wrong?
Thanks for your continued help.

"JLGWhiz" wrote:

Maybe these two subs can illustrate the difference a little better.
Create a
new workbook, insert a code module into the VBE and copy these two
snippets
into the code module. Then run the snippets to see how the same variable
'word' takes on different characteristics. In one case, it acts as an
object
which contains a string property, and in the other it is a variable equal
to
a string value.

Sub objTest()
Dim str As Range
Set str = ActiveWorkbook.Sheets(1).Cells(3, 3)
str = "Hello"
End Sub

Sub strValTest()
Dim str As String
ActiveWorkbook.Sheets(1).Cells(3, 3) = "Hello"
str = ActiveWorkbook.Sheets(1).Cells(3, 3).Value
MsgBox str
End Sub

"OlieH" wrote:

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in
that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.

JLGWhiz, thanks, just one more question. Can I use a variable for the row in
cells?
e.g. Set str = ActiveWorkbook.Sheets(1).Cells(I,3).
You have helped me a lot.

"JLGWhiz" wrote:

Did you copy the code that I gave you or do some modification to add your
range references in? Both snippets performed as expected on my xl2003. The
first one put "Hello" in cell C3 and the second one displayed a message box
with "Hello"

The point is, that one of them uses the variable str as a range object,
where the other one uses the variable as a string data type. When you want
to use a variable as a range object, you have to use the Set keyword and
then make the variable equal to a range, which can inlude the workbook,
sheet and cell, but must be the cell reference as a minimum. When you want
to use the variable as a string, date, number or anything other than an
object, you do not use the Set key word, but you use the Value property of
the object for the variable value. If you look at the code snippets, you
will see that where Set is used, Value is not and in the other it is vice
versa.


"OlieH" wrote in message
...
When I tried to runthe first example (excel 2003) the set command
gave me a 438 error; Object doesn't support this property or method.
What am I doing wrong?
Thanks for your continued help.

"JLGWhiz" wrote:

Maybe these two subs can illustrate the difference a little better.
Create a
new workbook, insert a code module into the VBE and copy these two
snippets
into the code module. Then run the snippets to see how the same variable
'word' takes on different characteristics. In one case, it acts as an
object
which contains a string property, and in the other it is a variable equal
to
a string value.

Sub objTest()
Dim str As Range
Set str = ActiveWorkbook.Sheets(1).Cells(3, 3)
str = "Hello"
End Sub

Sub strValTest()
Dim str As String
ActiveWorkbook.Sheets(1).Cells(3, 3) = "Hello"
str = ActiveWorkbook.Sheets(1).Cells(3, 3).Value
MsgBox str
End Sub

"OlieH" wrote:

JLGWhiz, thanks for the reply. When I do as you say and I then
execuite the statement str = "Test" the value "Test" does not get set
on SUMlc line of workbokks(1).sheet(1)
thanks again

"JLGWhiz" wrote:

To assign and object to a variable, you must use the Set keyword.

Set str = Workbooks(1).Sheets(1).Cells(RR, 1)

If you are trying to make the variable apply to a string located in
that
cell, then:

str = Workbooks(1).Sheets(1).Cells(RR, 1).Value

"OlieH" wrote:

to shorten code, I wish to declare
str = Workbooks(1).sheets(1).cells(RR,1)
as an alias so that each time I
need to reference that cell I could use an aliase
in stead of workbooks(1).sheets(1).cell(RR,1) = "a"
I could just use str = "a"

Thank you very much for any help.