Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako vstaviti trenutni časovni žig, ko se podatki v drugi celici spremenijo v programu Excel?

Če prejmete delovni list, ki ga potrebujete za spreminjanje podatkov v določenem stolpcu. Po spremembi delovnega lista morate vnesti spremenjeni časovni žig, da drugim obvestite, katere celice so bile spremenjene. Kako ravnati z njo? Ta članek vam bo pokazal način vstavljanja trenutnega časovnega žiga, ko se podatki v drugi celici spremenijo v Excelu, kot je prikazano spodaj.

Vnesite trenutni časovni žig, ko se podatki v drugi celici spremenijo s kodo VBA


Vnesite trenutni časovni žig, ko se podatki v drugi celici spremenijo s kodo VBA


Kot je prikazano spodaj prikazano sliko, morate v stolpcu E zapolniti časovni žig, če se spremenijo ustrezne celice v stolpcu C. Prosimo, naredite na naslednji način.

1. Na delovnem listu morate spremeniti in označiti s časovnim žigom, z desno miškino tipko kliknite zavihek stanja in nato kliknite Ogled kode iz menija z desnim klikom.

2. Nato kopirajte in prilepite spodnjo kodo VBA v Koda okno Microsoft Visual Basic za aplikacije okno. Prikaz slike:

VBA koda: Vstavite trenutni časovni žig, ko se podatki v drugi celici spremenijo

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180830
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3
xTimeColumn = 5
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

Opombe: V kodi številka 3 pomeni, da boste podatke spremenili v stolpcu C, 5 pa označuje, da se časovni žig naselji v stolpec E. Prosimo, spremenite jih glede na vaše potrebe.

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej se pri spreminjanju podatkov v stolpcu C časovni žig naselije v ustrezne celice v stolpcu E, kot je prikazano spodaj.

Nasvet. Izbirnik datuma. Vstavi datum uporabnost Kutools za Excel vam pomaga hitro vstaviti datum z določenim datumskim formatom v izbrano celico. Pojdi na brezplačno prenesete programsko opremo če potrebujete. Prikaz slike:


Sorodni članki:



Priporočena orodja za produktivnost za Excel

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hannah · 1 days ago
    Many thanks for this - set it up yesterday and all worked perfectly. However I have updated cells today and the dates do no seem to update. I have also tried to duplicate the code on a second tab today and this is not working.

    Any ideas?
  • To post as a guest, your comment is unpublished.
    ajax · 1 months ago
    Hi Crystal,
    I am trying to create a function where If I update any Cell in H9:L9 in Sheet 1, timestamp in cell C2 in Sheet 2 should get updated.
    Now I need to do this on about 100 rows in Sheet 1( H10:L10, H11:L11) and update corresponding C3, C4....in Sheet 2

    Any help is greatly appreciated. Cheers.!
  • To post as a guest, your comment is unpublished.
    Luis · 1 months ago
    5/15/19|1:41 PM|John Smith|125B-1|10000000|Equipment|Staff|5/15/19|2:43 PM|Staff

    The above post shows the data that is input into a row. I was seeking help updating the first 2 columns that contain a timestamp whenever the ID (10000000) column is populated. If possible could the timestamps also be removed if the ID is also removed? Concerning the last 3 columns (date|time|staff) could those timestamps get updated whenever the last column is populated with a staff name. I tried playing around with the code but my lack of knowledge with VBA only allowed me to do so much.
  • To post as a guest, your comment is unpublished.
    JediTrader · 3 months ago
    Gents,

    It has been a month I am looking for a similar solution like this one. While the above solution seem to be OTM, I am using this for the stock market.
    So I have Column F where I have implemented a Buy/Sell strategy and the sheet continues to refresh every 1 minute. I have Column T where I want the time stamp based on the following conditions :

    (1) Column F - Signals a Buy or Sell
    (2) Column F - Changes from a Buy to Sell or Sell to Buy
    (3) Column F - Changes from a Buy/Sell to empty

    While the general solution that is available on the net seem to be working (excel formula), but when the data refreshes it punches the current time than the signal time. For e.g. If I get a Buy @ 9:15 hrs and if the current time is 10:30, I get to see 10:30 hours in the Column T (Signal time) and not 9:15 hours.

    Any assistance would be greatly appreciated.

    Warm Regards
    JT
  • To post as a guest, your comment is unpublished.
    spen · 3 months ago
    Would the timestamp functionality be bypassed if the excel file you're using is a refreshable?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Good day,
      The code does not support connections data. Sorry for the inconvenience.