Excel Use

Dim WkS  As Excel.Worksheet 
Dim myCell  As Excel.Range 
Dim sFormula  As String 
Suppose we want to look at all the cells on the worksheet WkS, and for each cell whose formula is "=Sheet1!C2" replace it by "=CompanyName." This would be useful if Sheet1!C2 contains the company name, but the range CompanyName, whose range is Sheet1!C2, didn't exist when the worksheet was first developed. Assume WkS is Set to an existing worksheet in an open workbook, and that UsedRange is a range that covers all cells used on the WkS worksheet. You would use the following code:
For Each myCell In WkS.UsedRange
If myCell.HasFormula Then
sFormula = myCell.Formula
If (sFormula = "=Sheet1!C2") Then
myCell.Formula = "=CompanyName"
End If
End If
Next
Failure to protect
myCell.Formula
by the preceding
If myCell.HasFormula Then
would cause the code to fail if the value contents of any myCell is longer than 1,024 characters. Make sure you protect any cell for which you reference .Formula by first checking its .HasFormula property! Also, .HasFormula will return False if myCell is empty.