Geek Answers Handbook

Excel to replace formulas with values

I have an Excel workbook (1) with about 9 sheets that draws and processes data from a second workbook (2).

After extracting the data from the workbook (2), I will need to replace the formulas in the workbook (1) with the resulting values ​​that were obtained by the formulas, from here I will save the book (1) using Results.

Is there a macro that can do this for me?

+8
vba excel-vba excel
source share
2 answers

Your new book has some basic code, for example:

Sub Value() Dim ws As Worksheet For Each ws In ActiveWorkbook.Sheets ws.UsedRange.Value = ws.UsedRange.Value Next End Sub
+8
source share

concatenate() seems to be used for me

So, for example, formula I, referring to a cell from another sheet, looks like this:

 =arrayformula(iferror(index('To Be Processed'!X:X,small(if($A$1='To Be Processed'!$Y2,row('To Be Processed'!X:X)),row((2:2))),"")))

and if I go to the formula:

 =concatenate(arrayformula(iferror(index('To Be Processed'!X:X,small(if($A$1='To Be Processed'!$Y2,row('To Be Processed'!X:X)),row((2:2))),""))))

and it puts the text value from the link cell in my second sheet.

Which may or may not be useful depending on how you fill out your sheets - I am not very good at VBA, but that means that I do more things manually :)

0
source share

More articles:


All Articles