how to give values to non-continguous cells simultaneously
 

what I tried to do is very simple: I'd like to let columnB has same
values at same position at column A.

column A columnB
1 1 row 1
row 2
2 2 row 3
3 3 row 4
row 5
4 4 row 6

I got a problem when I use the following code to:

columns(1).cells.specialcells(xlcelltypeconstants) .select
selection.offset(,1).value = selection.value

the result turns out to be somthing like:
1 1 row 1
row 2
2 1 row 3
3 1 row 4
row 5
4 1 row 6

I know I can do that with a loop through each cell in selection area in
column A.

I just wondering whether there is a non-loop way to give the values to
column B
simultaneously.

any help will be appreciated


--
xiang
------------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

you can try this

Sub copy_column()
With Columns("A")
Columns("B").Value = .Value
End With

End Sub


--


Gary


"xiang" wrote in message
...

what I tried to do is very simple: I'd like to let columnB has same
values at same position at column A.

column A columnB
1 1 row 1
row 2
2 2 row 3
3 3 row 4
row 5
4 4 row 6

I got a problem when I use the following code to:

columns(1).cells.specialcells(xlcelltypeconstants) .select
selection.offset(,1).value = selection.value

the result turns out to be somthing like:
1 1 row 1
row 2
2 1 row 3
3 1 row 4
row 5
4 1 row 6

I know I can do that with a loop through each cell in selection area in
column A.

I just wondering whether there is a non-loop way to give the values to
column B
simultaneously.

any help will be appreciated


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

thanks a lot, Gary

it works when row 2 and row 5 are empty. How about if there are some
values in
row 2 and row 5, and I only want to give selected values in columnA to
column B at corresponding positions. In other words, I don't want to
give non-selected values in column A to Column B.

is there any non-loop way to do this or simply not doable?

thanks for your response


--
xiang
------------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

If column B doesn't contain formulas:

Sub Efd()
Dim rng As Range
Set rng = Intersect(Selection.EntireRow, Columns(2))
rng.Formula = "=" & rng(1, 0).Address(0, 0)
Columns(2).Formula = Columns(2).Value
End Sub

--
Regards,
Tom Ogilvy


"xiang" wrote in
message ...

thanks a lot, Gary

it works when row 2 and row 5 are empty. How about if there are some
values in
row 2 and row 5, and I only want to give selected values in columnA to
column B at corresponding positions. In other words, I don't want to
give non-selected values in column A to Column B.

is there any non-loop way to do this or simply not doable?

thanks for your response


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

try this

Option Explicit
Dim cell As Range
Sub copy_column()
With Range("A:a")
Debug.Print Selection.Value
Selection.Offset(0, 1).Value = Selection.Value
End With

End Sub

--


Gary


"xiang" wrote in
message ...

thanks a lot, Gary

it works when row 2 and row 5 are empty. How about if there are some
values in
row 2 and row 5, and I only want to give selected values in columnA to
column B at corresponding positions. In other words, I don't want to
give non-selected values in column A to Column B.

is there any non-loop way to do this or simply not doable?

thanks for your response


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

mine won't work, disregard it

--


Gary


"xiang" wrote in
message ...

thanks a lot, Gary

it works when row 2 and row 5 are empty. How about if there are some
values in
row 2 and row 5, and I only want to give selected values in columnA to
column B at corresponding positions. In other words, I don't want to
give non-selected values in column A to Column B.

is there any non-loop way to do this or simply not doable?

thanks for your response


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

Hi Gary, thaks for letting me know

--
xian
-----------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...fo&userid=2948
View this thread: http://www.excelforum.com/showthread.php?threadid=49259

how to give values to non-continguous cells simultaneously
 

Tom, your code is very very cool and works perfectly.
can I ask one more question if you don't mind?

the amazing part of your code:

rng.Formula = "=" & rng(1, 0).Address(0, 0)

as far as I know, rng(1,0) refers to range(A1) so that rng(1,
0).Address(0, 0) woud return A1, which in turn indicates rng.formula
would be "=A1", and the result
would turn out to be give A1.value to all areas in rng.

but your code is not like what I thought. I don't know why it works so
good.
which part I am missing?

i am wondering whether you could give me more explanations?

many thanks


--
xiang
------------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

since A1 is relative in the formula, it is adjusted for each cell in which
it is entered. Same as if you drag filled the formula down the column.

--
Regards,
Tom Ogilvy

"xiang" wrote in message
...

Tom, your code is very very cool and works perfectly.
can I ask one more question if you don't mind?

the amazing part of your code:

rng.Formula = "=" & rng(1, 0).Address(0, 0)

as far as I know, rng(1,0) refers to range(A1) so that rng(1,
0).Address(0, 0) woud return A1, which in turn indicates rng.formula
would be "=A1", and the result
would turn out to be give A1.value to all areas in rng.

but your code is not like what I thought. I don't know why it works so
good.
which part I am missing?

i am wondering whether you could give me more explanations?

many thanks


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594

how to give values to non-continguous cells simultaneously
 

ok, now I see what I'm missing. appreciate your help.
another issue: :) if I change the following line
Columns(2).Formula = Columns(2).Value
to
rng.formula = rng.value

it won't work.
could you tell me why?


sorry to bother you agai

--
xian
-----------------------------------------------------------------------
xiang's Profile: http://www.excelforum.com/member.php...fo&userid=2948
View this thread: http://www.excelforum.com/showthread.php?threadid=49259

how to give values to non-continguous cells simultaneously
 

Because you can't use that construct on discontiguous cells.

If you have existing formulas, then I think you will need to loop.

--
Regards,
Tom Ogilvy


"xiang" wrote in
message ...

ok, now I see what I'm missing. appreciate your help.
another issue: :) if I change the following line
Columns(2).Formula = Columns(2).Value
to
rng.formula = rng.value

it won't work.
could you tell me why?


sorry to bother you again


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=492594