I need to write code in VBA Excel for a button.
The purpose of this button is to increment value in certain cell starting from 0 and I've done that already, but the second feature of this button should be that after clicking this button, current date and time would be shown in the A column starting from A2 how it looks in excel
So basically if value in "L13" is 1, current date and time should appear in "A2", if value in "L13"is 2, date and time should appear in A3 and so on up to let's say A200.
I've tried to make this doing different loops, but I couldn't get expected results.
Below is my current code for this button: https://github.com/Cuyer/excel/blob/main/add_click
Sub Add_Click()
Dim countCell As Range
Set countCell = ActiveSheet.Range("L13")
countCell = countCell 1
Dim wb As Workbook
For Each wb In Application.Workbooks
wb.Save
Next wb
End Sub
I would really appreciate any help in resolving this issue.
CodePudding user response:
Sub Add_Click()
Dim countCell As Range, ws As Worksheet
Set ws = ActiveSheet
Set countCell = ws.Range("L13")
countCell.Value = countCell.Value 1
'use Offset() to find the date cell
ws.Range("A1").Offset(countCell.Value,0) = Date
Dim wb As Workbook
For Each wb In Application.Workbooks
wb.Save
Next wb
End Sub
CodePudding user response:
The Date function in VBA will give you the Date value of the current day.
Then you can just assign it to a range's value:
Activesheet.Range("A" & countCell 1).Value = Date
I'm not sure if you mean the Date should appear in a single cell or in a number of cells equal to the value in "L13". The code above is for a single cell, but here is how you would do it for a number of cells:
ActiveSheet.Range("A2:A" & countCell 1).Value = Date