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

 

[yky]

You need something like "comment =
ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").comment.text"

 

[Robert774]

hmmm I get a error for debugging

 

[yky]

What is the error message?

Your code doesn't add the existing comment. That's why you don't get the old comment. This code, "& NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").comment.text", adds the existing comment to your string variable "comment."

Step through the code and see if "comment" now includes the existing comment.

Another thing is if the cell already has a comment, you can't use the ".AddComment" code. You just change the existing comment by using this:

NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text Text:= newcomment & oldcomment

 

[Robert774]

Hi, first of all thanks for you help because i am really not getting it. i am quite new in this and your explanations really help.

right now when i use the code NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text Text:= newcomment & oldcomment

i get an compile error: expected expression. not sure how to fix this. :D

 

[Robert774]

and with this bit Text:= newcomment & oldcomment "expected end of statement"

 

[Robert774]

Comment = ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text
with this i get bug 9 "content out of range"

 

[yky]

I guess you didn't write the code. When you said "It does copy the comment in the cell but does not take the old comment already written down in the cell with it ", which cell are you talking about? Which cell contained the old comment? I assume you were talking about Sheets(Jaar).Cells(StayIdx, "CT") but then the AddComment code would not work. So, can't be it.

 

[Robert774]

Well, I have a file where we push a lot of rate changes on a daily basis. We want to adjust the comments so we can see our rate change activity.

I Can download a report from our extranet with the rate change activity but now i want to convert this report into the comment cells.

there are three sheets. 2018,2019 and 2020 and the information sheet with the comments

The cells i want to place the comments in are colum " CT"

now i am this far that the code does copy the comment in the cells. But there are old comments written in the cells which i want to keep because we would like to track the changes.

this is for example already written in the cells.

i have no idea how to copy this information and add the new information from the tab comments to the comment and place it in the same cell.

i hope it is a little bit clearer now what i try to do. :D

thanks in advance! :D

kind regards,

Robert

 

[yky]

The code doesn't really "copy" comments. It gathers information from different cells and concatenates them into a string. ExtractCap(User) gets the initial of a user; ModDate gets the date; OldMargin gets the old rate; NewMargin gets the new rate.

The comment you showed includes four different dates. Is this done manually? I'm asking because the code you supplied can do only one date. If it is not done manually, you need to search your code to see if there are other codes that manipulate comment.text.

That said, try the following for loop, i.e. replace the for loop in your code with the following code. Always try it on a copy of your workbook.

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

    Jaar = Format(StayDate, "yyyy")

    StayIdx = DateDiff("d", CDate("1/1/" & Jaar), StayDate) + 2

    On Error GoTo handler

   If Sheets(Jaar).Cells(StayIdx, "CT").Comment Is Nothing Then

   Comment = ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin

    Sheets(Jaar).Cells(StayIdx, "CT").AddComment (Comment)

  Else

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

 Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text Text:=Comment

  End If


handler:

    Resume Next

Next i