VBA to copy Cell.Formula to cell comment

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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.

Code:
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
504
The line:
CellComment = cell.Formula
should be:
CellComment = CellInRange.Formula
 

Watch MrExcel Video

Forum statistics

Threads
1,090,490
Messages
5,414,849
Members
403,549
Latest member
CascadeDiver

This Week's Hot Topics

Top