How to Apply Formula to every cell in range and print result for each cell in cell zz new line

[Tardisgx]

Excluding blank cells

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

So I put this UDF together. It shows a cell as it would appear in VBA. R1C1 format



My goal is to be able to "translate" for lack of a better term; each cell in a sheet range into VBA.



This means I can instantly take an existing workbook and produce VBA to recreate it.



So 1)show a user select box for specifying a range. Then Click Proceed and 2) the above UDF reads for every cell in the specified range and 3) prints each cell's result on a new line in cell ZZ.



is easy enough to googlefu

but 2) & 3) I do not know how I would write it

 

[RoryA]

You should be aware of the forum rules on cross-posting by now, so please comply with them by adding the relevant links here to your posts in other forums. Thanks.

 

[Tardisgx]

https://www.reddit.com/r/excel/comments/brndla/how_to_apply_formula_to_every_cell_in_range_and/

Solution literally just occured found here

Sub BuildList()
    Dim c As Range
    Dim rngInput As Range
    Dim rngOutput As Range
    Dim i As Long

    'Ask our user for stuff
    Set rngInput = Application.InputBox("What cells do you want to read?", "Input", , , , , , 8)
    Set rngOutput = Application.InputBox("Where do you want output to go?", "Output", , , , , , 8)

    Application.ScreenUpdating = False

    'Write the answers
    For Each c In rngInput.Cells
        rngOutput.Cells(1).Offset(i).Value = ShowFormula(c)
        i = i + 1
    Next c
    Application.ScreenUpdating = True
End Sub
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

 

[RoryA]

That's not the only place you posted it...

 

[Tardisgx]

https://www.reddit.com/r/vba/commen..._range_and/?utm_source=share&utm_medium=web2x
https://www.reddit.com/r/excel/comm..._range_and/?utm_source=share&utm_medium=web2x
https://stackoverflow.com/questions...in-range-and-print-result-for-each-cell-in-ce

Original unmodifed udf http://www.howtoexcelatexcel.com/fo...rmulas-in-excel-with-a-user-defined-function/