How to work with the Excel UsedRange.Formula "Array"?? 2

[waldemar]

I am trying to find specific formulas in an Excel workbook. Cycling through all the cells of all the sheets wouldnt make any sense (too long). I found something like a Formula "Array" in the Range Object (I'm using the UsedRange for all references), that seems to list all formulas in the workbook. Can somebody give me an example how to cycle though these formulas?

This test function doesnt seem to work:

Function cleanup()
Dim rng As Range, sheet As Worksheet, intCounter As Long, varFormula1 As Variant, varFormula2 As Variant

Set rng = Sheets("testsheet&quot.UsedRange
For Each varFormula1 In rng.Formula
Debug.Print varFormula1
If InStr(1, varFormula, "=S&quot > 0 Then
MsgBox "found reference formula: " & varFormula1
End If
Next varFormula1
End Function

 

[sfvb]

You have:
If InStr(1, varFormula, "=S&quot > 0 Then
but you need:
If InStr(1, varFormula1, "=S&quot > 0 Then

 

[waldemar]

Cool, so this loop actually works!
But how do I delete a formula now that I found one (replacing Msgbox()...) ?

 

[247]

I would change the function a bit.

Function cleanup()
   Dim rng     As Range
   dim cell    As Variant 
               
   Set rng = Sheets("testsheet").UsedRange
   For Each cell In rng.Cells
      If cell.Formula <> &quot;&quot; Then
         Debug.Print cell.Address & &quot; formula is &quot; & cell.Formula
      End If
      If InStr(1, cell.Formula, &quot;=S&quot;) > 0 Then
         cell.Formula = &quot;&quot;
      End If
   Next cell
End Function

 

[waldemar]

Prefect 247, thanks a lot. I though looping through all Cells would slow this down too much. But works great!

 

[waldemar]

Now, wait, now I have a strange new problem:

Although the external reference (actually I was looking for "=V" (lookup)) is not available I had content in the cells (maybe some kind of default values?); by erasing the cell.formular (="&quot this content is also gone. Is there a way to keep it?

 

[rmikesmith]

waldemar,

Try this:

Function cleanup()
   Dim rng     As Range
   dim cell    As Variant 
   Dim SaveValue as Variant
            
   Set rng = Sheets("testsheet").UsedRange
   For Each cell In rng.Cells
      If cell.Formula <> &quot;&quot; Then
         Debug.Print cell.Address & &quot; formula is &quot; & cell.Formula
      End If
      If InStr(1, cell.Formula, &quot;=S&quot;) > 0 Then
         SaveValue = cell.Value
         cell.Formula = &quot;&quot;
         cell.Value = SaveValue
      End If
   Next cell
End Function

HTH

Mike

 

[waldemar]

Perfect

 

[waldemar]

No.... not completely solved yet....

Hmmm.... it seems Sheets("testsheet&quot.UsedRange doesnt cover all cells (and all formulas). I have still some formulas left over in Header cells.... Oh what should I use instead?.... (And does anyone know why IntelliSense doesnt work in Excel VBA?=

 

[waldemar]

Getting closer.... UsedRange works fine, but there is some cells with a really strange behavior:

These Cells seem to have an external Reference of some sort. I can not find any reference in any ob the cells properties not even the formula!! When you move your mouse pointer over the cell, a QuickInfo box appears however shows the path to the reference. (where is that saved?)

Am I having ghost refernces?

 

[waldemar]

[Talking to myself again ] It's even better... It is not really an external reference, but the complete path to the same excel file pointing to a sheet that doesnt exist (obviously was deleted). The question remains.... where can I locate and delete that reference? It is neither in the formula nor in the hyperlink etc....