Switching Cell Value with Cell Comments across all sheets

[sabascal]

Hi All,

I am trying to write a small sub to switch across all my active workbook sheet, cell values with cell comments.

It work in a given selection.
I am struggling to run it across active cell on each of my workbook sheets.

Can somebody help?
Thanks

---------------
Sub SwitchValueCOmment()
Dim Buffer1, Buffer2, Buffer3 As String

On Error Resume Next
Worksheets(1).Activate
Application.ActiveCell.Select
'ActiveSheet.Range("B12:B19&quot.Select
For Each cell In Selection
If cell.Comment.Text <> "" Then
Buffer1 = cell.Value
Buffer2 = cell.Comment.Text
Buffer3 = Replace(Buffer2, "Real Estate User:", "&quot
cell.Value = cell.Comment.Text
cell.Comment.Text Text:=Buffer1

End If
Next

End Sub

 

[combo]

This should go with all worksheets:

Dim r As Range, c As Range, wks As WorkSheet
For Each wks in ActiveWorkbook.Worksheets
On Error Resume Next
Set r = wks.UsedRange.SpecialCells(xlCellTypeComments)
If Err.Number = 0 Then
On Error GoTo 0
For Each c In r
' do the stuff
Next c
End If
Next wks

You can consider using c.Text instead of c.Value (no error when c has a number).

combo

 

[sabascal]

concise , efficient & working - Thanks combo