I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks

Use with statement

with WorkSheetObject
varname = .Cell1 & .Cell2
end with

"PJ" wrote:

I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks

with worksheets("sheet1")
csz = .Range("C17").value & .Range("D17").value & .Range("E17").value
end with

I like to specify the property I want to use, too. But maybe you'd want
..text???


PJ wrote:

I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks

--

Dave Peterson

VJ;535948 Wrote:
Use with statement

with WorkSheetObject
varname = .Cell1 & .Cell2
end with


which translates to:
With Sheets("Sheet1")
csz = .Range("C17") & .Range("D17") & .Range("E17")
End With


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147318

Dim ws as Worksheet, csz as String
Set ws = Sheets("Sheet1")
csz = ws.Range("C17") & ws.Range("D17") & ws.Range("E17")
Msgbox csz


If this post helps click Yes
---------------
Jacob Skaria


"PJ" wrote:

I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks

And, for a different method, which can easily be expanded to as many
contiguous cells in a row (just change the Range)...

Set R = Sheets("Sheet1").Range("C17:E17")
With WorksheetFunction
csz = Join(.Transpose(.Transpose(R)), "")
End With

If you Dim your variables (and you should), Dim csz as a String and R as a
Range. Now, the above code is good for rows; if you had a contiguous range
of cells in a column, then you would need to remove one of the Transpose
function calls (and, with that simplification, we could get rid of the
With..EndWith structure as well...

Set R = Sheets("Sheet1").Range("A10:A99")
csz = Join(WorksheetFunction.Transpose(R), "")

Also, using either of the above methods (depending on if your data is in a
row or in a column), you can easily specify a delimiter to place between
your cell values... just replace the "" with whatever that delimiter should
be (in quotes, of course). For example, to put a comma between the values,
just use "," instead of "".

--
Rick (MVP - Excel)


"PJ" wrote in message
...
I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks