VBA that reads a range in a sheet and prints in a cell the occupied cells values as VBA code?

[Tardisgx]

The idea is to print each cell contents as VBA new line after new line in a specified cell (ZZ) so the result can be pasted into a personal.xlb macro

(The VBA I write below is approximate, not proper syntax) Imagine the range we are printing occupied cells for is A100:I100 special select constants and formulas.

E.G If cell A1 had: Recycle Please

ZZ would then print

Sheets("SheetA"). Range("A1")R1C1="Recycle Please"

Then the next occupied cell across A10 would cause a new line in ZZ with the contents and location of that cell translated into VBA and so on.

Does such a thing exist and if it doesn't how do I get started making such a thing, I have my own experience with VBA.

A formula cell in b7 =SUM(D1*D5)
would print as a new line in cell ZZ
Sheets("SheetA"). Range("b7").FormulaR1C1 = "=SUM(RC[3]*R[4]C[3])"

Is there a formula or udf to translate cells or formulas in R1C1 format?

 

[Tardisgx]

Found this UDF and modified it to show R1C1 format now I just need need to add activesheet.name and the cell location to the udf; not sure how to do that right though.

Function showformula(rng As Range)
If rng.HasArray = True Then
    showformula = "{" & rng.Formula & "}"
    Else
        showformula = rng.FormulaR1C1
    End If
End Function

 

[Tardisgx]

Function showformula(rng As Range)
If rng.HasArray = True Then
    showformula = "{" & rng.Formula & "}"
    Else
        showformula = "Sheets(""" & ActiveSheet.Name & """). Range(""" & rng.Address & """)" & ".FormulaR1C1 = " & """" & rng.FormulaR1C1 & """"
    End If
End Function

Made This. Does what I wanted.