VBA to set date and update counter in a cell...

billbane

New Member
Joined
Jun 19, 2018
Messages
6
Hi, wondering if someone can help write some VBA code for an Excel sheet I have?

When I type CTRL-R, I'd like to have:

1) the current date update in column G of the row where the active cell is, format 'm/d/yyyy'.

2) a "1" added to the value in the cell column H of the row where the active cell is; currently that cell could have a # in it already, or it could be empty.

Many thanks!!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Code:
sub updateit
cells(activecell.row,"G") = format(now(),"MM/DD/YYYY")
cells(activecell.row,"H") = 1
end sub

Then edit macro and Options, set macro to Ctrl+R
 
Upvote 0
Oh that gets me really close to where I want to be, many thanks!! Is there a way to have "1" ADDED to the whatever cell value is in col H or the active cell row? Right now, that cell either is blank or has another number in it. For example, if it is blank, I want it to change to 1 after running the macro. Alternatively, for example, if there is a 4 in col H of the row of the active cell, I want that cell to change to 5 after running the macro. Thank you again!!
 
Upvote 0
Awesome, many thanks! It almost does what I need it to do. The date is updating perfectly. The 'counter' needs a little help. Does anyone know how to have "1" added to the value in column H of the active cell's row? Column H of the active cell's row might be blank/empty to start, or it might already have a # in it. If it is blank, I'd like it to flip to 1; if it has a number in it already, I'd like it to flip to the next higher whole number, e.g. 4 goes to 5 or 10 goes to 11... Many thanks!!
 
Upvote 0
Sub updateit()
Cells(ActiveCell.Row, "G") = Format(Now(), "M/D/YYYY")
Cells(ActiveCell.Row, "H").Value = Cells(ActiveCell.Row, "H").Value + 1
End Sub

That solves the problem, thanks all.
 
Upvote 0
Sub updateit()
Cells(ActiveCell.Row, "G") = Format(Now(), "M/D/YYYY")
Cells(ActiveCell.Row, "H").Value = Cells(ActiveCell.Row, "H").Value + 1
End Sub

That solves the problem, thanks all.

Sorry about not replying back quickly but it looks like you figured it out.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top