VBA - Using a table to replace text in comments

[Kimbil]

Hello there, I looked around and couldn't find an answer to this specifically.

Basically I'm trying to write a macro that finds text that occurs in both the comments of a worksheet and a table (found on the "Translations" sheet). Then replaces the aforementioned text with the adjacent cell data from the table.

Here is the code I have so far:

Sub Translate_Comments()​

Dim c As Range, rng As Range​

With Sheets("Translations")​

Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)​

End With​

With ActiveWorkbook.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeComments)​

For Each c In rng​

.Replace c.Value, c.Offset(, 1).Value, xlPart, MatchCase:=True​

Next c​

End With​

End Sub​

And it's not working... I have written the code to do this for the cell data in the sheets, seen below, but I can't figure out how to do it for the comments too, anyhelp would be appreciated.

Functional code:

Sub Translator()​

Dim c As Range, rng As Range​

With Sheets("Translations")​

Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)​

End With​

With ActiveWorkbook.Sheets("Sheet1")​

With Intersect(.UsedRange, .Range("A:AA"))​

For Each c In rng​

.Replace c.Value, c.Offset(, 1).Value, xlPart, MatchCase:=True​

Next c​

End With​

End With
End Sub​

Thank you.