Hardcode

[Steph]

Hello. Is there a way to have vba scan an entire sheet (activesheet) and
hardcode any cells that have a VLookup formula in the cell? I was going to
just hardcode the entire sheet, but i also have a lot of sum formulas that I
can't have hardcoded. Thanks.

 

[Guest]

One way:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Cells
If UCase(Left(rCell.FormulaR1C1, 8)) = "=VLOOKUP" Then rCell.Value =
rCell.Value
Next rCell

HTH

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No Formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.Formula Like "*VLOOKUP(*" Then
myCell.Value = myCell.Value
End If
Next myCell
End With
End Sub

But it will get fooled with formulas like:
=a1&"Steph, don't forget to include your =VLOOKUP() formula here"

But that's probably not too likely?????