Hi - I need a macro to copy the plain text value in B1 to the firs
empty cell in column A. Any help will be greatly appreciated.
Thanks

--
elrussel
-----------------------------------------------------------------------
elrussell's Profile: http://www.excelforum.com/member.php...fo&userid=3102
View this thread: http://www.excelforum.com/showthread.php?threadid=51812

Hi

this may not be very sophisticated but it works

Sub CopCell()

ActiveSheet.Range("B1").Select
Selection.Copy
ActiveSheet.Range("a1").Select
If Selection.Value = "" Then 'checks if A1 is free already
GoTo InsertValue
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
GoTo InsertValue
End If


Exit Sub
InsertValue:
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


Good luck
Udo

Range("A1").End(xlDown).value = range("B1").value

HTH

--
AP

"elrussell" a écrit
dans le message de
...

Hi - I need a macro to copy the plain text value in B1 to the first
empty cell in column A. Any help will be greatly appreciated.
Thanks.


--
elrussell
------------------------------------------------------------------------
elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=518125

Ooops!
Range("A1").End(xlDown).offset(1,0).value = range("B1").value

--
AP

"Ardus Petus" a écrit dans le message de
...
Range("A1").End(xlDown).value = range("B1").value

HTH

--
AP

"elrussell" a
écrit
dans le message de
...

Hi - I need a macro to copy the plain text value in B1 to the first
empty cell in column A. Any help will be greatly appreciated.
Thanks.


--
elrussell
------------------------------------------------------------------------
elrussell's Profile:
http://www.excelforum.com/member.php...o&userid=31020
View this thread:
http://www.excelforum.com/showthread...hreadid=518125

Hi Ardus,

that's really good, I like it, much better and straight forward than my
suggestion!
It only has a minor problem: if column A is not yet filled at all, it
will put the value into A2, not A1. So perhaps we should write:
if Range("A1").Value = "" then
Range("A1").value= range ("B1").Value
else
Range("A1").End(xlDown).offset(1,0).value = range("B1").value
End if

Udo

thanks everyone! I appreciate the good thoughts.


--
elrussell
------------------------------------------------------------------------
elrussell's Profile: http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=518125

The code below works great - but only once. That is, it does not move
down the empty column on subsequent calls. The content is placed
correctly in the first empty cell, but the code does not seem to be
able to detect that here is new content in the cell and move on. Do I
need to initialize a variable? I have tried testing the cell several
ways:


If Range("E9").Value = " " Then
If Range("E9").Value = 0 Then


neither detect the empty cell.
Here is the code that works 'once'.



Sub copyCell()

'L6 holds copy of Earned Points

If Range("E9").Value = "" Then
Range("E9").Value = Range("L6").Value
Else
Range("E9").End(xlDown).Offset(0, 0).Value = Range("L6").Value
End If

End Sub


--
elrussell
------------------------------------------------------------------------
elrussell's Profile: http://www.excelforum.com/member.php...o&userid=31020
View this thread: http://www.excelforum.com/showthread...hreadid=518125