VBA to copy Cell.Formula to cell comment

[AusSteelMan]

Hi everyone,

I have had a shot at some code that will check if a cell in the selected range has a formula and if so add this formula as a text string in the same cell's comments.

Public Sub CellFormulaToCellComment()

Dim CellInRange As Range
'Dim CellComment as String


For Each CellInRange In Selection
    If CellInRange.HasFormula Then
        CellComment = cell.Formula    '"this is a formula"
        CellInRange.ClearComments
        CellInRange.AddComment (CellComment)
    End If
Next
    
End Sub

When I run it i get a "Run-time error '424': Object required on the "CellComment = cell.Formula" line.
If I replace the cell.Formula with the string shown above, everything works.
So I know where my problem is, but apparently not knowledgeable enough to fix it (or I do know but am having a moment of fogginess )

I tried commenting out the Dim CellComment As String in case that was contributing too. No such simple luck.

Can anyone please correct my code as I got sick of manually copy and pasting?

Many thanks in advance
Cheers,
Darren

 

[dcardno]

The line:
CellComment = cell.Formula
should be:
CellComment = CellInRange.Formula

 

[AusSteelMan]

So it was fogginess then!!

Thanks for finding my very silly mistake.

Cheers