Thursday, November 5, 2009

excel macro to replace text in a cell and add it as a comment

Sub Versive()
x = 3
Dim presentvalue, commentvalue
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B3:B" & lastrow)
For Each c In MyRange
If InStr(c.Value, "(") > 0 Then
presentvalue = c.Value
commentvalue = Mid(c.Value, InStr(c.Value, "("))
presentvalue = Replace(presentvalue, Mid(c.Value, InStr(c.Value, "(")), "")
Cells(x, 2) = presentvalue
With Cells(x, 2)
If Not .Comment Is Nothing Then .Comment.Delete
.AddComment Text:=commentvalue
.Value = presentvalue
'.Shape.TextFrame.AutoSize = True
End With
Cells(x, 2).Comment.Shape.ScaleWidth 1.49, msoFalse, msoScaleFromBottomRight
Cells(x, 2).Comment.Shape.ScaleHeight 2.14, msoFalse, msoScaleFromBottomRight
x = x + 1
End If
Next
End Sub

No comments: