Copy comments in existing comment tabs

[Robert774]

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:

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

 

[Robert774]

WOW! great it works!! thank you very much.

there is only one more thing to make it the best code in the world!

as you can see the latest date is now on the bottem. it would be great if the latest date modified would be at the top.

Is this possible to program in the code or not?
I looked on the internet but could not really find something useful

kind regards and thank you in advance,

Robert

 

[Robert774]

WOW! great!! it works!!

There is only on more thing before it would be the best code in de world.

As you can see the latest date is on the bottem but I would like to have it at the top. This because it would be in chronological order when posting new data in the tab comments.I laready looked


I already looked on the internet to figure out how to do this but i could not find anything useful

I hope you could help me out.

Kind regards and thanks in advance! :D

Robert

 

[yky]

Try reversing the order of the following code. Change

Comment = ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin & vbLf & Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text

To

Comment = Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text  & vbLf & ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin

 

[Robert774]

YES!! it worked! thank you very much for your help!!

 

[Robert774]

hello back again. hahaha

Now i get this some. dates are correct but I think it filters on initial now as well.

 

[yky]

Check your data. If it is in chronologic order, the simple code should work. If not, then, it won't work. Maybe you want to sort your data before running the code.