+ Reply to Thread
Results 1 to 9 of 9

VBA to store range of cells as text!

  1. #1
    Forum Contributor
    Join Date
    02-09-2015
    Location
    malta
    MS-Off Ver
    Microsoft 365
    Posts
    383

    VBA to store range of cells as text!

    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

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA to store range of cells as text!

    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

    Please Login or Register  to view this content.
    You could always acquire Excel 2016 which contains a new function =FORMULATEXT !!
    Last edited by kev_; 12-01-2016 at 05:23 AM.

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: VBA to store range of cells as text!

    or
    Please Login or Register  to view this content.
    edited
    or maybe
    Please Login or Register  to view this content.
    Last edited by nilem; 12-01-2016 at 05:21 AM.

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to store range of cells as text!

    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:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    02-09-2015
    Location
    malta
    MS-Off Ver
    Microsoft 365
    Posts
    383

    Re: VBA to store range of cells as text!

    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!

  6. #6
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to store range of cells as text!

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

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA to store range of cells as text!

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

  8. #8
    Forum Contributor
    Join Date
    02-09-2015
    Location
    malta
    MS-Off Ver
    Microsoft 365
    Posts
    383

    Re: VBA to store range of cells as text!

    Cheers guys

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA to store range of cells as text!

    You are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 09-16-2016, 03:52 PM
  2. store all the values of cells which are in range
    By starlev in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-23-2015, 05:33 PM
  3. Store range of cells in another worksheet and assign ID to row
    By cajand in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2014, 07:33 AM
  4. [SOLVED] Lock/Unlock range cells based on text value in other range cells
    By boboivan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-12-2014, 12:50 PM
  5. [SOLVED] Delete text, split numbers to two cells and store as numbers
    By Steve_123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2014, 09:51 AM
  6. [SOLVED] Store consecutive cells range
    By fgq in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2013, 09:51 AM
  7. Number store as text & text date with 2 digit year problem
    By obc1126 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-24-2008, 01:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1