Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form command not working on worksheet with merged cells | Excel Discussion (Misc queries) | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
Adding Merged cells together | Excel Discussion (Misc queries) | |||
Avoiding page breaks across merged cells - Code not working as expected | Excel Programming | |||
Copy/Paste Merged Cells via Macro is not working | Excel Programming |