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 in Others by narikkadan
• 63,000 points
89 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 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,000 points
240 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 in Others by narikkadan
• 63,000 points
128 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 in Others by narikkadan
• 63,000 points
243 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
628 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
2,714 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
• 22,980 points
182 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,630 points
443 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 in Others by Kithuzzz
• 38,010 points
174 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 in Others by Kithuzzz
• 38,010 points
999 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