Hello,
I have a problem. I can not figure out how to post a new comment into an oldcomment. When i try to it does not copy the oldcomment with it. It does copy the comment in the cell but does not take the old comment already written down in the cell with it. The code I am using right now is:
Does anyone know how to fix this problem?
I really hope someone know.
kind regards and thanks in advance!
Robert
I have a problem. I can not figure out how to post a new comment into an oldcomment. When i try to it does not copy the oldcomment with it. It does copy the comment in the cell but does not take the old comment already written down in the cell with it. The code I am using right now is:
Code:
Sub Button1_Click()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim N As Integer
Dim StayDate As Date
Dim ModDate As Date
Dim User As String
Dim NewMargin As Integer
Dim OldMargin As String
Dim Comment As String
Dim StayIdx As Integer
Dim OldComment As Variant
Dim NewComment As Variant
N = Range("A1").End(xlDown).Row
For i = 4 To N
StayDate = Cells(i, 1).Value
ModDate = Format(Cells(i, 2).Value, "Short Date")
User = Cells(i, 3).Value
NewMargin = Cells(i, 6).Value
OldMargin = Cells(i, 8).Value
Comment = ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin
Jaar = Format(StayDate, "yyyy")
StayIdx = DateDiff("d", CDate("1/1/" & Jaar), StayDate) + 2
On Error GoTo handler
Sheets(Jaar).Cells(StayIdx, "CT").AddComment (Comment)
handler:
Resume Next
Next i
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Function ExtractCap(str As String)
ExtractCap = ""
For f = 1 To Len(str)
If Asc(Mid(str, f, 1)) >= 65 And Asc(Mid(str, f, 1)) <= 90 Then
ExtractCap = ExtractCap & Mid(str, f, 1)
End If
Next f
End Function
Does anyone know how to fix this problem?
I really hope someone know.
kind regards and thanks in advance!
Robert
Last edited by a moderator: