VBA Magician required again

[mikemcbain]

I have had such success on this Forum that I will cheekily test your generosity once again!

Each day I import several hundred rows of data in columns A:K of a xlsm workbook interspersed randomly with blank rows and I must manually put today's Date into Column O:O of each row containing data.

Would some wizard produce a little VBA code for me that I can use to insert today's date into Column O:O?

I would like to add it to the end of the macro that imports the other data to this workbook.

I use Office 365 and Windows 10 and I am 82yo and starting to slow up.

Mike.

 

[Michael M]

This assumes there should be data in "K"

Sub MM1()
 Dim cell As Range
    For Each cell In Range("K1", Cells(Rows.Count, "K").End(xlUp))
        If cell.Value <> "" Then cell.Offset(, 4).Value = Date
   Next cell
End Sub

 

[My Aswer Is This]

This script will do what you want if we are looking down Column A for blank cells.

Sub Add_Date_To_Column_O()
'Modified 5/11/2019 2:21:59 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, 1).Value <> "" Then Cells(i, "O").Value = Date
Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Mike
In addition, simply put a line before the End Sub of your import code saying....

Call MM1
End Sub

That way the date code can stay seperate...

 

[mikemcbain]

WOW - My answer Is and Michael M - so quick so complete and so simple!

Many thanks to you both and tomorrow I will advise that it is working perfectly.

Mike.

 

[Fluff]

If col K is values, rather than formulae, here is another option

Sub mikemcbain()
   Range("K:K").SpecialCells(xlConstants).Offset(, 4).Value = Date
End Sub

 

[My Aswer Is This]

Glad I was able to help you. As you will see with Excel Vba there are 20 ways to do everything.

WOW - My answer Is and Michael M - so quick so complete and so simple!

Many thanks to you both and tomorrow I will advise that it is working perfectly.

Mike.

 

[mikemcbain]

G'day Fluff, My answer is and Michael M

Thank you all very much everything working perfectly and I am actually using some of each of your suggestions.

Another 5 - 10 minutes a day saved and at my age that is very important!

Although I wish were young enough to learn VBA for myself this excellent resource that Mr Excel provides serves me well.

Mike
Tasmania.

 

[Michael M]

Glad we could help Mike....Cheers mate...

 

[mikemcbain]

Michael M

It would improve my operation even more if you could modify the following to only add the Date to the Offset 4 column O if the cells were empty because I do have a heading and other info high up in that column which is currently being overwritten.

Sub MM1()
Dim cell As Range
For Each cell In Range("K1", Cells(Rows.Count, "K").End(xlUp))
If cell.Value <> "" Then cell.Offset(, 4).Value = Date
Next cell
End Sub

With thanks

Mike.