VBA Magician required again

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
This assumes there should be data in "K"

Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
This script will do what you want if we are looking down Column A for blank cells.

Code:
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
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Mike
In addition, simply put a line before the End Sub of your import code saying....

Code:
Call MM1
End Sub
That way the date code can stay seperate...(y)
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,415
Office Version
  1. 365
Platform
  1. Windows
If col K is values, rather than formulae, here is another option
Code:
Sub mikemcbain()
   Range("K:K").SpecialCells(xlConstants).Offset(, 4).Value = Date
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad we could help Mike....Cheers mate...(y)
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
107
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,307
Messages
5,635,456
Members
416,858
Latest member
yelofneb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top