Checking if a cell is blank
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Checking if a cell is blank

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Guest

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com