Copy comments in existing comment tabs

Robert774

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
365
Platform
Windows
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:



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:

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,449
You need something like "comment =
ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").comment.text"
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,449
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

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
365
Platform
Windows
and with this bit Text:= newcomment & oldcomment "expected end of statement"
 

Robert774

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
365
Platform
Windows
Comment = ExtractCap(User) & " " & ModDate & " " & OldMargin & ">" & NewMargin & Sheets(Jaar).Cells(StayIdx, "CT").Comment.Text
with this i get bug 9 "content out of range"
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,449
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

New Member
Joined
Dec 3, 2019
Messages
10
Office Version
365
Platform
Windows
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.
Capture 3.PNG


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

Capture 1.PNG


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




Capture5.PNG


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.

Capture4.PNG


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

Well-known Member
Joined
Jun 7, 2011
Messages
1,449
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.

VBA Code:
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
 

Forum statistics

Threads
1,078,373
Messages
5,339,814
Members
399,328
Latest member
Jasonabelly

Some videos you may like

This Week's Hot Topics

Top