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

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

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

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

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

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

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

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