Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Finding the next empty line

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    I want to start at row 5 and find the next empty line. Once it has found the line I want to add the formula =NOW() to the column A for the first empty row.

    Thank you.

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    To find the next blank cell in column B, place the following eqn in col A:

    =IF(COUNTBLANK($B$5:$B5)=1,NOW(),"")

    To label every blank row:
    =IF(COUNTBLANK(B5:IV5)=255,NOW(),"")

    To label the first blank row, if cell A4 is blank:
    =IF(AND(COUNT($A$4:$A4)=0,COUNTBLANK(B5:IV5)=255),NOW(),"")

    To label the first blank row, if cell A4 contains a number:
    =IF(AND(COUNT($A$4:$A4)=1,COUNTBLANK(B5:IV5)=255),NOW(),"")

    You can also investigate array formulas to do this type of thing.

    Regards,

    Brian.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Brian.

    How would I do this in VBA?

    Sub insert_info()
    Dim i As Integer

    Set FixWS = Workbooks("fx.xls").Worksheets("Report")

    rr = 5

    todaysDate = CLng(Int(Now()))

    If (FixWS.Cells(rr, 1).Value = "") Then
    rr = rr + 1
    Else
    FixWS.Cells(rr, 1).Select
    End If


    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry ignore last message. I want to try and do this in VBA. This is what I have:

    Sub insert_flow()

    Dim i As Integer

    Set FixWS = Workbooks("fx.xls").Worksheets("Report")

    rr = 5


    If (FixWS.Cells(rr, 1).Value = "") Then
    rr = rr + 1
    Else
    FixWS.Cells(rr, 1).Formula = "=NOW()"
    End If


    End Sub


    This doesn't seem to work.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone know how to do this in VBA?

  7. #7

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this :-

    Sub insert_flow()
    Dim rr#
    ActiveSheet.UsedRange
    rr = Cells.SpecialCells(xlCellTypeLastCell)).Row+1
    Cells(rr, 1).Formula = "=NOW()"
    End Sub


    [ This Message was edited by: dwhj on 2002-05-08 00:34 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply. I should have explained better. I already have some data there, i.e. in cells A5, A25, A45, A65.

    I want the code to insert the formula after A5. At the moment it goes to A66.

  9. #9
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Hi. Please Try this too.


    Sub Test()
    Workbooks("fx.xls").Worksheets("Report") _
    .[A5].End(xlDown).Offset(1).Formula = "=now()"
    End Sub

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  10. #10

    Join Date
    May 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or if you need to check all columns in each row instead of just column A :-

    Sub insert_flow()
    Dim rng As Range, cell As Range
    Set rng = Intersect([A4:A65536], ActiveSheet.UsedRange).Offset(1, 0)
    For Each cell In rng
    If Application.WorksheetFunction.CountA(Range(cell, cell(1, 256))) = 0 Then
    cell.Formula = "=NOW()"
    Exit For
    End If
    Next
    End Sub

Some videos you may like

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
  •