Can't get Formula property value from within the Range.Find method

[dmars]

What I'm trying to do is very simple - find all the formulas in a workbook that contain references for cells on sheets other than the current sheet and write the list of them to a sheet named "data".

The basic logic of the code is also simple: find all the formulas containing "!" in the workbook. (But I'm getting wrapped around the axles on what should be a very simple thing to do.)
The "data" sheet has 3 columns:
1) "Sheet" for sheet name,
2) "Addr" for cell address, and
3) "Formula". (I need this list to find all the testable test cases for another macro I'm writing.)

The problem is getting the Formula property of a found cell that has "!" in it. Here's my code:

Sub FindRefs()

   Dim wkbk As Workbook, shts As Sheets, sht As Worksheet, sheetname As Range, addr As Range, form As Range, c As Range
   Set wkbk = Workbooks("Current.xls")
   Set shts = wkbk.Worksheets
   Set sheetname = wkbk.Worksheets("data").Range("A2.A1000")
   Set addr = wkbk.Worksheets("data").Range("B2.B1000")
   Set form = wkbk.Worksheets("data").Range("C2.C1000")
   wkbk.Worksheets("data").Range("A2.C1000").Clear
   For Each sht In shts
      With sht.Range("a2:ab2734")
         Set c = .Find("!", LookIn:=xlFormulas)
         If Not c Is Nothing Then
            firstAddress = c.Address
            i = 2
            Do
               sheetname(i) = sht.Name
               addr(i) = c.Address
               form(i) = c.Formula
               Set c = .FindNext(c)
               i = i + 1
            Loop While Not c Is Nothing And c.Address <> firstAddress
         End If
      End With
   Next
End Sub

After running this code down past the "Set c = .Find("!", LookIn:=xlFormulas)" statement, in the Watch Window, c.Formula has the formula I want in it. BUT, form (i) has c.Value in it, and Excel's built-in IDE will not allow the statement "form(i) = c.Formula". It insists on revising it to "form(i)=c.formula", as if "formula" is a custom property (I guess).

I've tried a number of things, including writing a function which took "c" as an argument, but within this function "d.Formula" (where "d" is the passed object "c") behaves exactly like "c.Formula" does in the main procedure. Thinking that the problem is that "c" is a range, which could potentially have many formulas in it, (but then why does c.Formula have the correct formula in it?), I also tried to get the address of the first cell that "c" points to, hoping to get its formula, but that turned into a rat's nest of its own and I couldn't find a way to do it that worked. (and c.Address has the correct cell address in it anyway, I just can't use it in code.)

Maybe I just got too messed up dealing with it all and someone will know how to do this approach right so it works. I also tried to use c.FormulaR1C1, but that got even weirder. c.Address (in this instance) is "=Budget!G3", and c.FormulaR1C1 is "=Budget!R[-11]C[1]". Maybe I could use this if I knew which cell the R1C1 address is relative to. I know that the R[-11] means 11 rows above, but I couldn't see how G3 and R[-11]C[1] could point to the same cell (G3 = R3C7 from A1) in any way that makes any sense, and I have no idea in general which cell Excel would be calculating these relative addresses from. If I could get a good formula from c.FormulaR1C1 I could generate the normal cell address in code, but this seems so entirely unnecessary when what I want is sitting right there in c.Formula. Any help solving this mess would be greatly appreciated.

All I want to do is write the contents of c.Formula to the i-th cell in the "Formula" column. Simple, right? arg.

Please feel free to ask any questions if I haven't written this clearly enough.

 

[dmars]

Another oddity. Adding these lines to the code above:

Dim s As String

s = CStr(c.Formula)
form(i) = s

Stepping through this addition to the code above, s is the correct formula, but form(i) is still the cell value.

 

[dmars]

Also doesn't work to loop through and peel off 1 char at a time of the formula and put it into a string variable. The string variable will hold the correct formula, but when you assign it to the range array you get the cell value again.

Same result if you compute the cell address to store the formula in and assign the string variable (holding the formula) to that cell.

s = CStr(c.Formula)
t = Empty

For j = 1 To Len(s) + 1
t = t & CStr(Mid(s, j, 1))
Next

a = "c" & Format(i) 'C column is "Formula"
sht.Range(a) = t

 

[RoryA]

Hi,

I've tried to clean up your original post - hopefully I achieved that.

Just curious - you start i at 2 and your output range starts in row 2, which means that the first item you post to the range will actually go into row 3 (which is row 2 of the specified range) - was that intentional?

As regards the r[-11]C[1], the offsets are relative to the cell containing the formula ( so that's 11 rows up and one to the right of the formula cell, wherever that may be).

Because you are outputting a formula string, Excel will interpret it as a formula and calculate it - if you want to see the actual formula, use:

form(i) = "'" & c.Formula

 

[dmars]

How do you "cast" a Range variable, in this case "form", to be a range of strings? Would that allow me to write this string to a cell on a worksheet?

 

[dmars]

Success!!!
Instead of starting with t = Empty, I started with t = "'". Worked like a charm, and that was probably the problem all along.

Thanks, Rory.

Actually, starting with i=2 was a booboo. I was just so discombobulated by this formula property problem, that got by me. You are correct, it starts writing to the 3rd row. But that's easy to fix!!!

PS. This is my first time ever to post a question to a forum, and I really boogered it up. Sorry about that, but I'll get it right in the future.

 

[RoryA]

No need to apologise - layout aside (which I don't think was your fault), it was actually a very clear question: good detail about the problem, what you had tried and what the results were. All that makes it much easier for us to try and help you!

Welcome to the forum!

 

[dmars]

In case anybody's interested, here's the finished sub, and it does everything I wanted it to.

Sub FindRefs()
    
    Dim wkbk As Workbook, shts As Sheets, sht As Worksheet, sheetname As Range, addr As Range, form As Range, c As Range
    
    Set wkbk = Workbooks("Current.xls")
    Set shts = wkbk.Worksheets
    Set sheetname = wkbk.Worksheets("data").Range("A2.A1000")
    Set addr = wkbk.Worksheets("data").Range("B2.B1000")
    Set form = wkbk.Worksheets("data").Range("C2.C1000")
        
    wkbk.Worksheets("data").Range("A2.C1000").Clear

    i = 1
            
    For Each sht In shts
        
        '"data" (sheet we are writing to) is the last sheet and don't want a relisting of all the formulas already there.
        If sht.Name = "data" Then GoTo Done
        
        With sht.Range("a2:ab2734")
        Set c = .Find("!", LookIn:=xlFormulas)
        If Not c Is Nothing Then
            firstAddress = c.Address
            
            Do
                'only want strings starting with "=" (other strings contain "!" in workbook)
                If Asc(Left(c.Formula, 1)) = 61 Then
                    sheetname(i) = sht.Name
                    addr(i) = c.Address
                    form(i) = "'" & c.Formula
                End If
                
                Set c = .FindNext(c)
                
                i = i + 1
                
                End If
                
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
        End With
    Next
Done:
End Sub