-
1 Attachment(s)
How to Update Daily Log In Another Sheet
-
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
-
-
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
-
-
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.
-
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
