Macro to find next blank cell in a column

[LKulikamp]

I need to include a line that will find the next blank cell in column A and then run the macro.
Here is the macro as it is now:

Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A2").Select
Sheets("Sheet1").Select
Range("D2").Selec

 

[mrshl9898]

Like this?

Sub findblank()

Sheets("Sheet2").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


End Sub

 

[footoo]

Or :

Sub findblank()
Sheets("Sheet2").Select
[A1].End(xlDown)(2) = Format(Now(), "dd/mm/yyyy hh:mm AM/PM")
End Sub

Or perhaps :

Sub findblank()
Sheets("Sheet2").Select
Cells(Rows.Count, "A").End(xlUp)(2) = Format(Now(), "dd/mm/yyyy hh:mm AM/PM")
End Sub

 

[My Aswer Is This]

I think your trying to write a script to do a little more then just this:
It is good that your wanting to learn more about writing script's.
Why not tell us your full ultimate goal and let us write a script for you and maybe you can learn more from that.

All this selecting is not really needed

To enter a value into Sheet(2).Range("B5")

Only requires this:

Sheets(2).Range("B5").value=Now()

Like this:

Sub Now_Please()
Sheets(2).Range("B5").Value = Now()
End Sub

 

[Rick Rothstein]

You don't really have to select the worksheet to do what you asked for on it (of course, you can if you want to though)...

Sub FindBlank()
  Sheets("Sheet2").Columns("A").SpecialCells(xlBlanks)(1) = Now
End Sub