Document formulas on sheet 2

[boninm]

Hi everyone

I want to do a VBA script that will document on sheet2, all of the formulas from sheet1.

I got the loop going (a simple loop for now 5x5). Since I'm able to write to the cell, I'll be able to read it.
But I'm having an issue getting the cell reference from sheet1 to a cell on sheet2
As for the formula, I will use the FORMULATEXT function

That's where I'm stuck.

Thanks in advance.

------------
VBA Code:

Sub CycleThrough()
Dim VRows As Integer
Dim VColumns As Integer

For VColumns = 1 To 5
    For VRows = 1 To 5
        'Worksheets("Sheet1").Cells(Counter, 3).Value = CELL("address",Counter&3)
        VColumns2 = CStr(VColumns)
        VRows2 = CStr(VRows)
        Worksheets("Sheet1").Cells(VColumns, VRows).Value = VColumns2 & "," & VRows2 '(Columns 1st, then Rows)
    Next VRows
Next VColumns
End Sub

-------------------------

Document formulas on sheet2.xlsm
	A	B	C	D	E
1	1,1	1,2	1,3	1,4	1,5
2	2,1	2,2	2,3	2,4	2,5
3	3,1	3,2	3,3	3,4	3,5
4	4,1	4,2	4,3	4,4	4,5
5	5,1	5,2	5,3	5,4	5,5
6
7
8	Example		A	B
9		1	AFGSGF	=Z1+Z2
10		2	=MATCH(…..	=ETC…
11		3	=xlookup(…...	AKJDFH
12		4	ADGFSFG	A;KDLJF=
13		5	SFGSGF	=ETC…
14
15	Output on Sheet 2		If cell starts with "=", then Column A will have the Cell Reference, and Column B have the formulatext info
16
17			A2	=MATCH(…..
18			A3	=xlookup(…...
19			B1	=Z1+Z2
20			B2	=ETC…
21			B5	=ETC…
Sheet1

Cell Formulas
Range	Formula
D17:D18	D17	=C10
D19:D20	D19	=D9
D21	D21	=D13

 

[Sam_D_Ben]

Just to confirm, is this what you are looking for in sheet2?

Book1
	A	B	C	D
8	Example		A	B
9		1	Text1	=SUMPRODUCT
10		2	=Sum	=Vlookup
11		3	=Xlookup	Text4
12		4	Text2	Text5
13		5	=Text3	=EDate
Sheet1

Cell Formulas
Range	Formula
D10	D10	=Vlookup
C13	C13	=Text3
D13	D13	=EDate

Book1
	A	B
1	Cell Reference	Extracted Formula
2	D9	=SUMPRODUCT
3	C10	=Sum
4	D10	=Vlookup
5	C11	=Xlookup
6	C13	=Text3
7	D13	=EDate
Sheet2

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

Give this a try with a copy of your workbook.

Sub List_Formulas()
  Dim c As Range
  Dim i As Long
  
  i = 1
  With Sheets("Sheet2")
    .UsedRange.Clear
    .Columns("A:B").NumberFormat = "@"
    .Range("A1:B1").Value = Array("Cell", "Formula")
    For Each c In Sheets("Sheet1").UsedRange.SpecialCells(xlFormulas)
      i = i + 1
      .Range("A" & i).Resize(, 2).Value = Array(c.Address(0, 0), c.Formula)
    Next c
    .UsedRange.Columns.AutoFit
  End With
End Sub

 

[boninm]

Just to confirm, is this what you are looking for in sheet2?

Book1
	A	B	C	D
8	Example		A	B
9		1	Text1	=SUMPRODUCT
10		2	=Sum	=Vlookup
11		3	=Xlookup	Text4
12		4	Text2	Text5
13		5	=Text3	=EDate
Sheet1

Cell Formulas
Range	Formula
D10	D10	=Vlookup
C13	C13	=Text3
D13	D13	=EDate

Book1
	A	B
1	Cell Reference	Extracted Formula
2	D9	=SUMPRODUCT
3	C10	=Sum
4	D10	=Vlookup
5	C11	=Xlookup
6	C13	=Text3
7	D13	=EDate
Sheet2

yes

 

[boninm]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

Give this a try with a copy of your workbook.

Sub List_Formulas()
  Dim c As Range
  Dim i As Long
 
  i = 1
  With Sheets("Sheet2")
    .UsedRange.Clear
    .Columns("A:B").NumberFormat = "@"
    .Range("A1:B1").Value = Array("Cell", "Formula")
    For Each c In Sheets("Sheet1").UsedRange.SpecialCells(xlFormulas)
      i = i + 1
      .Range("A" & i).Resize(, 2).Value = Array(c.Address(0, 0), c.Formula)
    Next c
    .UsedRange.Columns.AutoFit
  End With
End Sub

I will try your code.

Thank you for the explanation about the code tags. new at this.
I will read your info.
Thanks again

 

[Peter_SSs]

Thank you for the explanation about the code tags. new at this.
I will read your info.
Thanks again

No problem - you are welcome.

I will try your code.

Seems like it must have worked for you.