Home > Blockchain >  Multiple codes in Excel
Multiple codes in Excel

Time:04-21

I want to have an excel spreadsheet that will automatically enter the date when information is selected from a drop down menu. But to do this multiple times in a year. If I create a formula it updates the the date every time it's opened. So I'm learning that a VBA code needs to be used instead so the date stays when the data is first entered. I don't want it to change every time the spreadsheet is opened.

I have found one code that will work, but I need this to be done on multiple columns if there is data selected from the drop down menu. For example, throughout the year, people would continually update the data and I want the date to show automatically when new data is entered into the next column. Another question too is will this transfer to Google Sheets?

Here's the code I found that seems to work on the first set of columns, E and F

Sub Worksheet_Change(ByVal Target As Range)
'
    Dim RowThatChanged  As Long
'
    If Not Intersect(Target, Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row)) Is Nothing Then     
        RowThatChanged = Target.Row                                                                     
        Range("F" & RowThatChanged).Value = Format(Now, "mm-dd-yyyy")                                   
        
    End If
End Sub

But I need it to do the same thing to these columns when the data is updated, (G & H), (I & J), (K & L), (M & N), for example.

CodePudding user response:

This will work for excel.

Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, rng As Range
    
    If Target.CountLarge > 1000 Then Exit Sub 'some reasonable limit on how many cells to handle...
    'in a worksheet code module `Me` is the worksheet itself
    Set rng = Application.Intersect(Target, Me.Range("E:E,G:G,I:I,K:K,M:M")) 'for example
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            c.Offset(0, 1).Value = Format(Now, "mm-dd-yyyy") 'add date one column over
        Next c
    End If
End Sub

However if your real target is Google Sheets you should ask a question specifically about that.

  • Related