Checking if a cell is blank

[P5X]

I want to paste data into a cell but I only want to do it if the selected cell is blank, if it isn't then the data should be posted in the next blank cell in the column.
Any ideas on how to do this would be appreciated.

 

[Tom Morales]

p5x
If you're adding information to a given column, say Column A, then the code below would find the next blank cell in column A, and, in this case, paste the contents of range C8 there.
Sub pleasepaste()
[C8].Copy
[A65536].End(xlUp).Offset(1, 0).PasteSpecial
End Sub
Tom

 

[P5X]

Thanks Tom, that worked great.
I only want about 40 cells that allow data to be input into, how do you get a message box to pop up once the limit has been reached?
thanks

 

[Barrie Davidson]

On 2002-02-17 10:10, P5X wrote:
Thanks Tom, that worked great.
I only want about 40 cells that allow data to be input into, how do you get a message box to pop up once the limit has been reached?
thanks

Something like this:

If Range("A65536").End(xlDown).Offset(1, 0).Row > 40 Then Exit Sub

This will exit the sub if the row number is greater than 40. You can change the number to suit your purposes.

 

[P5X]

perhaps you misunderstood, I have got the limit set to 40 by modifying Tom's statement:

[A40].End(xlUp).Offset(1, 0).PasteSpecial

What I want now is an error message to come up once the limit has been reached.

ps-i'm not too good with excel as you've probably figured so if you're statement did a similar thing then sorry

 

[Tom Morales]

Following up on Barrie's suggestion...

If [A65536].End(xlup).Offset(1, 0).Row > 40 Then
MsgBox "Sorry...no more room"
Exit Sub
else
[A65536].End(xlUp).Offset(1, 0).PasteSpecial
end if
This message was edited by Tom Morales on 2002-02-17 16:03
This message was edited by Tom Morales on 2002-02-17 16:07

 

[Guest]

Barrie and Tom wrote :-
If Range("A65536").End(xlDown).Offset(1, 0).Row > 40

This should read :-
If Range("A65536").End(xlUp).Offset(1, 0).Row > 40

 

[Tom Morales]

Yeah, I copied it, and then realized the error. Thx.
Tom