VBA to store range of cells as text!

**Keibri** · 12-01-2016, 04:58 AM

Hi all,

If it is not too much to ask, I was wondering whether someone can help me with the below.

Basically, I have a sheet, "Sheet1", which contains a number of formulas stored in cell range A1:AZ1000.

Would it be possible to write a VBA which when executed will store all these formulas as text?

Appreciate a lot your help!

Thanks

Keibri

**kev_** · 12-01-2016, 05:14 AM

You do not say where you want the values placing. Here is something you can modify:
The code below places the formula 26 columns to the right of the original cell (ie starting at cell AA1)
-modify to suit

I have only included up to AZ10 in the range to make testing speedier!
- amend it after testing to AZ1000

Sub Formula_as_text()

Set myrange = Sheets("Sheet1").Range("A1:AZ10").SpecialCells(xlCellTypeFormulas, 23)
    For Each cell In myrange 
        cell.Offset(0, 26).Value = "'" & cell.Formula
    Next
End Sub

You could always acquire Excel 2016 which contains a new function =FORMULATEXT !!

**nilem** · 12-01-2016, 05:19 AM

or

Sub ttt()
Range("A1:Z1000").Replace "=", " =", xlPart
End Sub

edited
or maybe

ActiveWindow.DisplayFormulas = True

**kasan** · 12-01-2016, 05:20 AM

Yeah, and do you really need to convert formula to text (you need to get "=A1+A2"?) or just convert result of formula?
This will convert all range to values:

Sub Pastevalues()

    Sheets("Sheet1").Range("A1:AZ1000").Copy

    Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

End Sub

**Keibri** · 12-01-2016, 05:26 AM

Cheers guys!! testing all solutions! I need to convert formula to text like kasan pointed out "=A1+A2"!

Thanks a lot for your help!

If I can also ask one last questions, what would be the VBA to convert the range back to its original format please?

Appreciate a lot your help guys!

**kasan** · 12-01-2016, 05:59 AM

Just a tip: try out Ctrl + ~ while Sheet is active. This will show formulas, press it again and you will see results of formulas.

**kev_** · 12-01-2016, 06:11 AM

There is an option to show all formula on the Formulas tab
ShowFormula.jpg

**Keibri** · 12-02-2016, 03:32 AM

Cheers guys

**kev_** · 12-02-2016, 06:18 AM

You are welcome.

VBA to store range of cells as text!

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Re: VBA to store range of cells as text!

Thread Information

Users Browsing this Thread

Similar Threads

Mulitple text options in range of cells equals unique values in different range of cells

store all the values of cells which are in range

Store range of cells in another worksheet and assign ID to row

[SOLVED] Lock/Unlock range cells based on text value in other range cells

[SOLVED] Delete text, split numbers to two cells and store as numbers

[SOLVED] Store consecutive cells range

Number store as text & text date with 2 digit year problem

Bookmarks

Bookmarks

Posting Permissions