VBA Help - Insert columns if there are any comments

[kreepa]

I hope this is an easy one.

I want to insert an adjacent column IF there are any comments in a column.

I would like to then use the following code to insert the comments into the empty fields:

Sub ShowCommentsNextCell()
Application.ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

For Each mycell In commrange
If mycell.Offset(0, 1).Value = "" Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell

Application.ScreenUpdating = True

End Sub

Can this code be modified to run through all sheets?

I then run a merge macro to combine ll the sheets.

My head hurts.

Thanks

 

[Fazza]

Yes, kreepa, it should be easy enough. However, I think it will be easier instead of checking a whole worksheet to work one column at a time through each worksheet. You will also have to consider how many columns you want to add - due to the column limit of 256. That is, if you have 200 columns and want to add 100 more it won't work. Another tip, it might be easiest to work from the rightmost column to the left as you loop through the columns on the worksheet - it is simpler this way as you add columns on the fly.

Regards,
Fazza

 

[kreepa]

I see your point. I would like to go row by row and only insert a column IF there are any comments in the querried column.

For Example --> If there areno comments in A1 but there is a comment in A2 the macro would insert a blank column B.

Additionally, if every row in column A had a comment the macro would insert a blank column B, NOT a hundred empty columns.

 

[Fazza]

kreepa,

I understand your requirement; you initially described it quite well.

Column by column is a simpler way to go, not row by row. You can check if the rightmost column has comments, if so add a column and then one by one the comments. Work through the columns heading left.

If you go row by row, you have an extra step to code - to know if there is already a column added. It just adds unnecessary complexity.

KISS,
Fazza