Results 1 to 7 of 7

Thread: How to Update Daily Log In Another Sheet

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Last edited by DocAElstein; 07-09-2023 at 10:40 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    try this

    Code:
    Option Explicit
    
    Sub kTest()
        
        Dim t, r As Long
        
        t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")
        r = Sheet2.Range("a1").CurrentRegion.Rows.Count
        
        If IsError(t) Then
            Sheet2.Range("a" & r + 1).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
            Sheet2.Range("n" & r + 1).Resize(, 5) = Sheet1.Range("e19:i19").Value
        Else
            If MsgBox("Entry already exists for date '" & Format(Range("'Entry '!e4").Value, "dd-mmm-yy") & "'" & vbLf & _
                    "Do you want to overwrite the entry?", vbYesNo) = vbYes Then
                Sheet2.Range("a" & t).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
                Sheet2.Range("n" & t).Resize(, 5) = Sheet1.Range("e19:i19").Value
            End If
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    WOW simply awesome. Working great.
    Can i put a condition if someone try to update the data for a date beyond 24 hrs, it will not allow to update the data (Just to prevent data manipulating for older record)

    If this is not too much of work for you, else i am fine with the exixting code.

    Thanks a TON.
    Rajesh

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
    https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-01-2024 at 02:33 PM.

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Admin,

    If i change the range in the below line from E4 to any other range say D4 the overwrite function doesn’t works. It keeps make duplicate entry. Not sure if I am doing something worng.

    Code:
    t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")
    Thanks
    Rajesh

  5. #5

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    try

    Code:
    Option Explicit
    
    Sub kTest()
        
        Dim t, r As Long
        
        t = Evaluate("MATCH('Entry '!E4,DataStore!A1:A10000,0)")
        r = Sheet2.Range("a1").CurrentRegion.Rows.Count
        
        If IsError(t) Then
            Sheet2.Range("a" & r + 1).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
            Sheet2.Range("n" & r + 1).Resize(, 5) = Sheet1.Range("e19:i19").Value
        Else
            If Date - CDate(Sheet1.Range("e4").Value2) > 0 Then Exit Sub
            If MsgBox("Entry already exists for date '" & Format(Range("'Entry '!e4").Value, "dd-mmm-yy") & "'" & vbLf & _
                    "Do you want to overwrite the entry?", vbYesNo) = vbYes Then
                Sheet2.Range("a" & t).Resize(, 13) = Application.Transpose(Sheet1.Range("e4:e16").Value)
                Sheet2.Range("n" & t).Resize(, 5) = Sheet1.Range("e19:i19").Value
            End If
        End If
        
    End Sub
    as far as changing the range, see the space after 'Entry '!E4. Ensure you spelled it correctly.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Admin,

    The space was causing issue, i removed the space and working fine now.
    Also the modified code to verify the date is also woking great, only thing i did is added 'Else' after Exit Sub.

    Thanks for your help.
    Rajesh

Similar Threads

  1. Access spreadsheet specifies the log
    By marreco in forum Excel Help
    Replies: 3
    Last Post: 02-07-2013, 04:13 AM
  2. Log In To Email Account Using VBA
    By Excel Fox in forum Download Center
    Replies: 1
    Last Post: 12-14-2012, 11:59 PM
  3. Combobox VBA update value worksheet
    By Tony in forum Excel Help
    Replies: 4
    Last Post: 10-28-2012, 12:28 AM
  4. Volatile Function To Update Or Refresh Only Once
    By StevenC in forum Excel Help
    Replies: 2
    Last Post: 05-11-2012, 03:43 PM
  5. Replies: 2
    Last Post: 11-17-2011, 07:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •