Need help/idea regarding a VBA code (repost)

[Fufu146]

due to some issues on the forum my post from yesterday has been deleted. some short info of what I have posted yesterday:

I have two calendars (sheet11 and sheet19) which have the exact formats.
Each cell represents one day of a year. In sheet11 there are different values than in sheet19 in the corresponsing cell.

So I would like to have a code, which takes the value of the cell in sheet19 and place it as an comment in the same cell in sheet11.

If sheet11.B2 not empty then
take value of sheet19.B2 and insert value as comment in sheet11.B2

basically he should do this for each cell...



I have tried the code which has been posted yesterday here (thanks again for this one!):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F8:NF75")) Is Nothing Then
Dim ans As String
Dim rr As String
rr = Target.Address
ans = Target.Value
If Sheets("Sheet11").Range(rr).Value <> "" Then

Sheets("Sheet11").Range(rr).AddComment.Text Text:=Target.Value
Else
Sheets("Sheet11").Range(rr).Value = Target.Value
'Sheets(1).Range(rr).Value = Target.Value
End If
End If
End Sub


this is what happens:

after entering a value in sheet19 I get the message:

run time error '1004'
Application defined or object defined error

I see that a comment is placed in sheet11 but without the value I was looking for, it's just empty.

when clicking on debug, it highlights following part of the code:

Sheets("Sheet11").Range(rr).AddComment.Text Text:=Target.Value
Any ideas on that one?

Thanks again!

 

[baitmaster]

Try
Sheets("Sheet11").Range(rr).AddComment Text:=Target.Value

You also need an error handler to decide what to do if that cell already contains a comment

 

[Fufu146]

just tried your code, it gives me the same error message and highlights that one as well.

 

[Worf]

due to some issues on the forum my post from yesterday has been deleted

Same problem for me...

 

[baitmaster]

I tidied the code slightly and referred to the VBA worksheet name ("code name") rather than the Excel worksheet name, this works OK for me

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F8:NF75")) Is Nothing Then
    Dim ans As String: ans = Target.Value
    Dim rr As String: rr = Target.Address
    
    If Sheet2.Range(rr).Value <> "" Then
        Sheet2.Range(rr).AddComment Text:=ans
    Else
        Sheet2.Range(rr).Value = ans
    End If
End If
End Sub

The VBA worksheet code name is that shown in the VB Editor NOT in brackets. If you use this like I have here, your code doesn't crash when the worksheet gets renamed. It works much better in VBA so get used to using it. If this code still breaks when you try and run it, it's probably because your cell already contains a comment. You'll need to test if it does and amend the existing comment rather than add a new one

 

[baitmaster]

the test is pretty simple, here's an update

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F8:NF75")) Is Nothing Then
    Dim ans As String: ans = Target.Value
    Dim rr As String: rr = Target.Address
    
    If Sheet2.Range(rr).Value <> "" Then
        If Not Intersect(Sheet2.Range(rr), Sheet2.Cells.SpecialCells(xlCellTypeComments)) Is Nothing Then Sheet2.Range(rr).Comment.Delete
        Sheet2.Range(rr).AddComment Text:=ans
    Else
        Sheet2.Range(rr).Value = ans
    End If
End If
End Sub

 

[Fufu146]

this is great, thanks a lot! this is exactly what I wanted...it works fine.

is there a way to make excel recalculate all the cells of a sheet at once? for now i need to click on the cell and press enter to make sure that the comment is placed on the other sheet.
The thing is that I won't type in the cell value manually, because the cells in the calendar are linked to a table and will take the values from there.

 

[baitmaster]

Of course. Excel will do most things that you can possibly conceive of, the hardest part is realising it's potential

So you want to compare all cells in the range at once? And when do you want to do this? It needs something to trigger the routine - when the sheet recalculates, when you activate it, when you hit a button...

Just beware, any code that writes a million and one cell comments will quickly slow down and should be used carefully

The following will mostly meet your needs, although it will still crash if there are no cell comments on the destination sheet at all - get round this by either adding some extra code, or having a dummy comment somewhere else on the worksheet

Sub compareCells()
Dim shtSource As Worksheet: Set shtSource = Sheet1
Dim shtDest As Worksheet: Set shtDest = Sheet2

Dim clSource As Range, clDest As Range
For Each clSource In shtSource.Range("F8:NF75")
    Set clDest = shtDest.Range(clSource.Address)
    
    If clSource <> "" And clDest <> "" And clSource <> clDest Then
        If Not Intersect(clDest, shtDest.Cells.SpecialCells(xlCellTypeComments)) Is Nothing Then clDest.Comment.Delete
        clDest.AddComment Text:=CStr(clSource)
    Else
        clDest = clSource
    End If
Next clSource
End Sub

 

[Fufu146]

thanks! getting much closer now. I tried your fast code, but it takes him ages to calculate since excel is going through the whole range I guess.

I decided to stick to following code, which delivers the result by updating the value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F8:NF75")) Is Nothing Then
Dim ans As String: ans = Target.Value
Dim rr As String: rr = Target.Address

If Sheet19.Range(rr).Value <> "" Then
Sheet11.Range(rr).AddComment Text:=ans
Else
Sheet19.Range(rr).Value = ans
End If
End If
End Sub

Its fine to recalculate the cell and add the comment by pressing enter in sheet11.
With this code excel puts the value of the cell in Sheet11 into the comment in Sheet11, in other words, the value of the cell itself appears again in the comment.
What do I have to change if I want excel to insert the value of the cell in Sheet19 into the comment in Sheet11?

 

[Fufu146]

alright, did some changes on the code. this code does what I have mentioned in my previous post.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range("F8:NF75")) Is Nothing Then
Dim ans As String: ans = Sheet19.Range(Target.Address).Value
Dim rr As String: rr = Target.Address

If Sheet19.Range(rr).Value <> "" Then
Sheet11.Range(rr).AddComment Text:=ans
Else
Sheet19.Range(rr).Value = ans
End If
End If
End Sub

Im sure there is a more elegant way to do that, but it delivers what I need