Using VBA to search workbook and hard code cells containing specific formulas

[Cole_T]

Hi,

I've seen some similar posts, and attempted to tweak the VBA in order for it to work for mu purposes, but I am relatively new to this and I haven't come up with anything that works properly yet.

I am looking to search through my workbook (quite a few sheets) to select and hard code all cells containing a VLOOKUP. From what I've read it seems that if you actually have the macro search through every cell in each sheet this will be pretty slow. In a few forums I'v noticed people using additional code combined with the VBA function to search and hard code the values being searched for which allows the macro to move quicker. I was also wondering how I could design this so that it will hard code the cells even if the VLOOKUP is not the first excel function in the formula.

As I mentioned before, I am pretty new to using VBA. Any advice and additional resources you can recommend to solve this issue and others in the future is greatly appreciated. I would post what I have written already, but if I'm being honest I don't think it will be useful whatsoever.

Thanks,

Cole

 

[MrTeeny]

Have a play around with this

Sub lookUp()
    For Each cell In ActiveSheet.UsedRange.Cells
        If cell.Formula Like "*VLOOKUP*" Then
            cell.Formula = cell.Value
        End If
    Next cell
End Sub

 

[MrTeeny]

To loop thru your workbook you'd do something like

Sub lookUp()

For Each aSheet In ActiveWorkbook.Worksheets


    For Each cell In aSheet.UsedRange.Cells
        If cell.Formula Like "*VLOOKUP*" Then
            cell.Formula = cell.Value
        End If
    Next cell


Next aSheet


End Sub

 

[Rick Rothstein]

This should be noticeably faster (especially if you have lots of filled cells) than visiting every cell individually...

[table="width: 500"]
[tr]
	[td]Sub ConvertVLOOKUPtoValues()
  Dim WS As Worksheet, FirstAddress As String, C As Range
  For Each WS In Worksheets
    With WS.UsedRange
      Set C = .Find("VLOOKUP", , xlFormulas, xlPart, SearchFormat:=False)
      If Not C Is Nothing Then
        FirstAddress = C.Address
        Do
          C.Value = C.Value
          Set C = .FindNext(C)
          If C Is Nothing Then Exit Do
        Loop While C.Address <> FirstAddress
      End If
    End With
  Next
End Sub[/td]
[/tr]
[/table]