Selecting cells with a particular formula

  • Thread starter Thread starter Muusbreath
  • Start date Start date
M

Muusbreath

Is it possible to select all cells in a range with a particular formula, for
instance, VLOOKUP?
 
Vlookup returns a string. It doesn't return a reference to a cell. You can
use Match to return an offset into a range, then use that to select a cell.
 
Thanks, but let me be more specific:

I have a column that uses to VLOOKUP to get values from a trial balance.
The same column has subtotals (SUM). I want to select only the cells with
the formula VLOOKUP so that I can copy>paste special>values leaving the SUM
formula intact.

Thanks for your help.
 
Dim cell as Range

With Worksheets("Sheet2")
for each cell in intersect(.Columns(6), .UsedRange).SpecialCells(xlFormulas)
if Instr(1,cell.Formula,"vlookup",vbTextCompare) then _
Cell.Formula = Cell.Value
Next
End With
 
Back
Top