Is it possible to write a macro that will automate the following in a worksheet:
Firstly, I would like to paste value each cell that starts with the formula '=HPVAL'
Secondly, I would like to clear each cell that starts with the formula '=IF'
Finally, I would like to clear each cell which start with 'P' and is followed by 5 numeric numbers i.e. P60400.
Alan Ryder
Replies (3)
Please login or register to join the discussion.
Not the most elegant but
I think this does it
'code starts
Sub DoIt()
Dim aCell
Dim aRef As Boolean
For Each aCell In ActiveSheet.UsedRange.Cells
aRef = False
If Left(aCell.Formula, 6) = "=HPVAL" Then
aCell.Copy
aCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
If Left(aCell.Formula, 3) = "=IF" Then aCell.ClearContents
If Len(aCell) = 6 _
And _
Left(aCell, 1) = "P" _
And _
IsNumeric(Mid(aCell, 2, 1)) _
And _
IsNumeric(Mid(aCell, 3, 1)) _
And _
IsNumeric(Mid(aCell, 4, 1)) _
And _
IsNumeric(Mid(aCell, 5, 1)) _
And _
IsNumeric(Mid(aCell, 6, 1)) Then aRef = True
If aRef Then aCell.ClearContents
Next aCell
End Sub
'code ends