VBA Help - Extract Formulas and Cell Location from a Range

[Johnny Thunder]

Hi group,

I am working on a spreadsheet that has a row of formulas that are from the range ("BB4:BX4") what I am trying to do is get a list of the cell location and the formula within that cell. Anyone have any code that can do that?

I would need the results on "Sheet1" in cell A2.

Example on Sheet1

[TABLE="width: 250"]
<tbody>[TR]
[TD]A2[/TD]
[TD]BB4[/TD]
[TD]"=IF(BJ4=TRUE,MAX(BB$2:BB3)+1,"""")"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]BC4[/TD]
[TD]"=IF($BC3="""","""",IF(BF3=BF2,BH2+1,1))"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]BD4[/TD]
[TD]"=IF($BC3="""","""",IF(BG3=BG2,BI2+1,1))"[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hopefully that makes sense.

Thanks in advance!

 

[hotabae]

Well, I threw together something that does that sort of thing, just not formatted quite how you'd like.

Maybe it will get you moving in the right direction?

(It copies the location and formula of the range D1:E4 into the Cells A1:B4)

Sub cellInfo()


    Dim formulaRng As Range
    Set formulaRng = Sheets("Sheet1").Range("D1:E4")
    
    Dim rowNum As Integer
    Dim colNum As Integer
        
    For rowNum = 1 To formulaRng.Rows.Count
       
        For colNum = 1 To formulaRng.Columns.Count
       
            Sheets("Sheet1").Cells(rowNum, colNum).Value = formulaRng(rowNum, colNum).Address & "-" & formulaRng(rowNum, colNum).Formula
   
        Next colNum
        
    Next rowNum


End Sub

If you wanted to "Output" the results to a column instead of matching the layout of the range, you could use this sorta thing:

[LEFT][COLOR=#222222][FONT=Verdana]
Sheets("Sheet1").Cells(i, 1).Value = formulaRng(rowNum, colNum).Address & "-" & formulaRng(rowNum, colNum).Formula
i = i + 1
[/FONT][/COLOR][/LEFT]

 

[Johnny Thunder]

@hotabae

your code worked great! Thanks for the help.