Combine a range with a constant number
 

I have looked for something on this and have yet to find the answer...
I have a range in Workbook1 sheet1 column A that I need to copy to...
1 2 3 4 etc..
Workbook2 sheet1 column A, But I need to add the number 908 before the
copied data...so when it is pasted into Workbook 2 its value is;
9081 9082 9083 9084 etc...

How do I go about doing this??


Thanks,

Hans

Combine a range with a constant number
 

Set sh1 = workbooks("Workbook1.xls").Worksheets("Sheet1")
Set sh2 = Workbooks("Workbook2.xls").Worksheets("Sheet1")
sh1.Range("A1:A10").copy Destination:=sh2.Range("A1")
for each cell in Sh2.Range("A1:A10")
cell.value = clng("908" & cell.Text)
Next

--
Regards,
Tom Ogilvy



" wrote:

I have looked for something on this and have yet to find the answer...
I have a range in Workbook1 sheet1 column A that I need to copy to...
1 2 3 4 etc..
Workbook2 sheet1 column A, But I need to add the number 908 before the
copied data...so when it is pasted into Workbook 2 its value is;
9081 9082 9083 9084 etc...

How do I go about doing this??


Thanks,

Hans

Combine a range with a constant number
 

Tom,

Thanks for the help, worked like a charm, but (there is always a but) I
did not think about this at the time, the copied range will vary in
length. I tried placing variations of this
....LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("F56:F"
& LastRow)
etc...
But could not get it to work...how would I do this at the same time? Is
there also a way to concetanate 2 columns during this process or is it
best to do before or after this has been completed.
You have helped me in the past either via personal help or your MASSIVE
amount of help to others...For me it is most greatfully appreciated!


Thanks again,

Hans

Combine a range with a constant number
 

Dim rng as Range
Set sh1 = workbooks("Workbook1.xls").Worksheets("Sheet1")
Set sh2 = Workbooks("Workbook2.xls").Worksheets("Sheet1")
set rng = sh1.Range("F56",Sh1.Range("F56").End(xldown))
rng.copy Destination:=sh2.Range("B9")
for each cell in Sh2.Range("B9").Resize(rng.rows.count,1)
cell.value = clng("908" & cell.Text)
Next


or if there will be spaces in the range

Dim rng as Range
Set sh1 = workbooks("Workbook1.xls").Worksheets("Sheet1")
Set sh2 = Workbooks("Workbook2.xls").Worksheets("Sheet1")
set rng = sh1.Range("F56",Sh1.Cells(rows.count,6).End(xlup))
rng.copy Destination:=sh2.Range("B9")
for each cell in Sh2.Range("B9").Resize(rng.rows.count,1)
cell.value = clng("908" & cell.Text)
Next

If you were going to concatenate columns, probably better to do it in the
destination if that is where you need it. Assume you want to concatenate
the cells from Column H starting with H56 in the Workbook1.xls, Sheet1 as an
example.

Dim rng as Range
Set sh1 = workbooks("Workbook1.xls").Worksheets("Sheet1")
Set sh2 = Workbooks("Workbook2.xls").Worksheets("Sheet1")
set rng = sh1.Range("F56",Sh1.Cells(rows.count,6).End(xlup))
rng.copy Destination:=sh2.Range("B9")
i = 1
for each cell in Sh2.Range("B9").Resize(rng.rows.count,1)
' concatenate here
cell.value = cell.Value & rng(i,3).Value
i = i + 1
Next






wrote in message
ups.com...
Tom,

Thanks for the help, worked like a charm, but (there is always a but) I
did not think about this at the time, the copied range will vary in
length. I tried placing variations of this
...LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("F56:F"
& LastRow)
etc...
But could not get it to work...how would I do this at the same time? Is
there also a way to concetanate 2 columns during this process or is it
best to do before or after this has been completed.
You have helped me in the past either via personal help or your MASSIVE
amount of help to others...For me it is most greatfully appreciated!


Thanks again,

Hans

Combine a range with a constant number
 

If you are working on a range with hundreds of values, this alternative
is more efficient because the assignments from and into the cells occur
without iteration.

There is iteration, but the iteration occurs over an array, not cells.

In this example the range B1:B3 is fetched, 9080 is added, and the
results are stored in C1:C3.

It should be clear how to go about changing the source and destination
ranges.


Sub G()
Dim rng1 As Range, rng2 As Range
Dim i As Integer, n As Integer
Dim r As Variant
r = Sheets("Sheet1").Range("B1:B3").Value
For i = 1 To UBound(r)
For j = 1 To LBound(r)
r(i, j) = 9080 + r(i, j)
Next j
Next i
Sheets("Sheet1").Range("C1:C3").Value = r
End Sub

Combine a range with a constant number
 

Tom,

Have another question for you...On the concatenate section, it works
just fine. I also need to have a space between the 2 columns and need
to be in proper case. I have been able to set proper case on all the
other columns, but not sure where to place it here...


Thanks,

H