Adding comment to cells not working with merged cells
I have some code that adds a cells comments to the cell value as below:
Sub CommentsToCells() Dim cl As Range Dim rng As Range Dim cmt As Comment On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 For Each cl In rng Set cmt = cl.Comment cl.Value = cl.Value & " : " & cmt.text Next cl End Sub This works well with unmerged cells but when the cells are merged the cell value is updated but then the code stops with the error: Run-time error '91': Object variable or With block variable not set How can I get this to work for merged cells as well? |
Adding comment to cells not working with merged cells
All other cells in a merged set of cells should be empty. Only the first
cell actually has data. Skip around setting the new value of the cell, if it is empty. If Not IsEmpty(cl) Then cl.Value = cl.Value & " : " & cmt.Text -- Regards, Bill Renaud |
Adding comment to cells not working with merged cells
Correction (I forgot that the cell might be empty, but still have a
comment and not be merged): Sub CommentsToCells() Dim cl As Range Dim rng As Range Dim cmt As Comment On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 For Each cl In rng Set cmt = cl.Comment If IsEmpty(cl) _ Then cl.Value = " : " & cmt.Text Else cl.Value = cl.Value & " : " & cmt.Text End If Next cl End Sub Your code is crashing in the case where cl.Value is empty (merged or not). You cannot concantenate an empty variable with a string to get another string. -- Regards, Bill Renaud |
All times are GMT +1. The time now is 09:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com