How do I build a loop into this TimeStamp VBA Code

0 votes

In this code, I'm attempting to incorporate a loop.

It just updates the first value I paste into the range it is referencing; it does not update any of the other cells. Is there a simple method to loop this and update it when I paste a range of cells so that it moves on to the next cell in the row that doesn't equal a blank?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 2
xTimeColumn = 9
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    Else
        On Error Resume Next
        Set xDPRg = Target.Dependents
        For Each xRg In xDPRg
            If xRg.Column = xCellColumn Then
                Cells(xRg.Row, xTimeColumn) = Now()
            End If
        Next
    End If
End If
End Sub

If there is anything further I can do, kindly let me know.

In order to record the time, I was anticipating a timestamp function that would let me paste ranges into the spreadsheet. Except for the first cell in the pasted range, it is not updating.

Mar 28, 2023 in Others by narikkadan
• 63,420 points
344 views

1 answer to this question.

0 votes

As Target is a range, it may contain a number of Areas, and each Area may contain a number of Cells. You could use a loop to go through those two collections and modify each cell separately.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCellColumn As Integer
    Dim xTimeColumn As Integer
    Dim xRow, xCol As Integer
    Dim xDPRg, xRg As Range
    xCellColumn = 2
    xTimeColumn = 9
    
    Dim Area As Range
    For Each Area In Target.Areas
        Dim Cell As Range
        For Each Cell In Area.Cells
            xRow = Cell.Row
            xCol = Cell.Column
            If Cell.Text <> "" Then
                If xCol = xCellColumn Then
                   Cells(xRow, xTimeColumn) = Now()
                Else
                    On Error Resume Next
                    Set xDPRg = Cell.Dependents
                    For Each xRg In xDPRg
                        If xRg.Column = xCellColumn Then
                            Cells(xRg.Row, xTimeColumn) = Now()
                        End If
                    Next
                End If
            End If
        Next Cell
    Next Area
End Sub

You might want to add an exit condition if Target is too large. If the user makes a large worksheet edit, like clearing an entire column, this event might cause unnecessary slowdowns.

answered Mar 28, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How do I insert a WebP-image (".jpg") in Excel using VBA?

It's not currently on the list of ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,420 points
485 views
0 votes
1 answer

How do I run a VBA Sub routine continuously when working in a Workbook and not only when the Workbook is opened?

on Thisworkbook, put: Private Sub Workbook_Open() Call checkPW(True) End Sub Then ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,420 points
336 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
662 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
908 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,228 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
518 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
761 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,181 views
0 votes
1 answer

How do ask ChatGPT with API from Excel macros (vba)?

A few things that will help. Don't ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
1,663 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP