Finding the next empty line

[BenNV]

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.

[Brian]

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.

[BenNV]

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

[BenNV]

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.

[BenNV]

[BenNV]

Anyone know how to do this in VBA?

[dwhj]

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 ]

[BenNV]

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.

[Colo]

Hi. Please Try this too.

Sub Test()

  Workbooks("fx.xls").Worksheets("Report") _

  .[A5].End(xlDown).Offset(1).Formula = "=now()"

End Sub

[dwhj]

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