Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Form: findind specific blank cell (2003 SP2)

    Good afternoon

    How can I find and insert data into the next blank cell in a specific column, I have been trying all day and at one time could get the information to always populate one cell in the column but i have since copied, pasted, pasted, copied until nothing works <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    My latest attempt is as shown below

    Private Sub cmbEmployee_Click()
    Dim Irow As Long
    Dim Ws As Worksheet
    Set Ws = Worksheets("Lists")
    Dim LastCell As Range
    With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)
    If IsEmpty(LastCell) Then
    .Cells(Irow, 1).Value = Me.txtEmployee

    Exit Sub

    Me.txtAddCountry.Value = ""
    Me.txtAddCountry.SetFocus
    End With

    End Sub

    I do not even now is this works because if I don't put End With it says it expects and End If statement and if I do put the End With I get message such as the picture below.

    How does that song go 'mama told me there would be days like this' !!

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    You have forgotten to include a line

    End If

    somewhere below the line with If ... Then but above the End With line.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    In addition to the missing End If that Hans pointed out, I see two additional things.

    1- The line that reads:

    <code>
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)
    </code>

    finds the last non empty cell in column J. Therefore, the following IF statement will never see an empty cell except when there is nothing in column J.

    2- The line which reads:

    <code>
    .Cells(Irow, 1).Value = Me.txtEmployee
    </code>

    uses variable lrow to determine where to store the value. However, lrow is never assigned a value and will therefore always be the default zero. This will result in a run time error since zero is an invalid subscript for Cells.
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    Thanks Hans and Legare

    I have amended the code and it no longer errors, likewise it does nothing, there is no entry in the last line, can I try your patience a little longer and ask you to look as this code to see what now might be wrong

    Private Sub cmbEmployee_Click()

    Dim Irow As Long
    Dim Ws As Worksheet
    Set Ws = Worksheets("Lists")
    Dim LastCell As Range
    With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)
    If IsEmpty(LastCell) Then

    Exit Sub

    End If

    Me.txtEmployee.Value = ""
    Me.txtEmployee.SetFocus
    End With

    Cheers

    Steve
    End Sub
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    The code doesn't put anything into the last row. It just sets the object to that cell.

    You need some line like:
    LastCell.value = Me.txtEmployee

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    Thanks for the quick answer Steve

    Have I inserted that line in the right place

    Private Sub cmbEmployee_Click()

    Dim Irow As Long
    Dim Ws As Worksheet
    Set Ws = Worksheets("Lists")
    Dim LastCell As Range
    With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)
    If IsEmpty(LastCell) Then

    LastCell.Value = Me.txtEmployee

    Exit Sub

    End If

    Me.txtEmployee.Value = ""
    Me.txtEmployee.SetFocus
    End With

    because nothing is still being added?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    LastCell is not empty, so nothing happens.
    Try this:

    ' Move one cell down from the last filled cell
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
    ' Set value
    LastCell.Value = Me.txtEmployee

    Note that there is no If ... Then and End If any more.

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    Yeh!! Thanks Hans

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> --------------- <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> ----------------------- <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    Yup, that is what I said in my last message. LastCell is set to the last cell with a value in it, therefore the IF IsEmpty(LastCell) is NEVER true. Try:

    <code>
    Set LastCell = .Cells(.Rows.Count, "J").End(xlUp)
    </code>

    to set LastCell to the cell below the last cell that is not empty.
    Legare Coleman

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Perhaps this is being overly pedantic, but if the last cell in the column is NOT blank, then the End(XLUP) will actually stop at a non-empty cell so it would be true in this case. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    [Though if I were concerned about this I would test the last cell in the column not the destination....]

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    I do not understand. If the .End(XLUP) stops at a non-empty cell then are you saying that IsEmpty(LastCell) will be true?
    Legare Coleman

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>Brain-fart. I was thinking about it wrong. You are right, it will not be true (unless the column is blank or just has something in the last cell of the column). I was thinking in term of being in the last filled cell of the column, which the code will not do if teh last cell of the column is filled...

    Steve

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Form: findind specific blank cell (2003 SP2)

    Hey!!!!!! I am the one who is getting to that age. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

Posting Permissions

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