Results 1 to 6 of 6

Thread: Convert Excel Formulas That Contain Specific Function In To Value

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13

    Lightbulb Convert Excel Formulas That Contain Specific Function In To Value

    Rajan Verma has posted the following on 09-27-2013 03:44 PM:

    Many times i have seen this query on Excel discussion groups and forums to convert values for a specific formulas on worksheets. so i have decided to post a something about that. it might be little helpful for Excel users. here is a code which will convert formulas to values on every sheets, this code […]

    Convert Specific formula to value :

  2. #2

  3. #3
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13
    And now i think, you think i dont know right ?

    we are not converting all formulas to values,the header of this post is Convert "Specific" formula to value, which means if you have lots of formulas e.g Sumproduct(), if(),offset() or others. and you want only Sumproduct() should get convert into values, this code will do that for users.

    i hope i was able to explain what i did

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 12-14-2023 at 02:52 AM.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    So wouldn't this suffice in that case?


    Code:
    Sub M_snb()
        on error resume next
        c00=inputbox("formula")
    
        for each sh in sheets
          for each cl in sh.Cells.SpecialCells(-4123)
            if instr(cl.formula,c00) then .Value = .Value
          next
        next
    End Sub
    Last edited by snb; 09-28-2013 at 01:46 AM.

  5. #5
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13
    Faster ?

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    The verb 'suffice' has nothing to do with speed, but with amount of code.

    You didn't mention anything about speed in the opening post in this thread.

    If you are interested you are free to compare the speed of my suggestion to that of your code....

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. TO convert Excel entire workbook in csv format
    By pritee in forum Excel Help
    Replies: 11
    Last Post: 08-16-2013, 11:28 AM
  3. Replies: 8
    Last Post: 07-01-2013, 03:52 PM
  4. Learning To Write Excel Formulas And Functions
    By venkat1926 in forum Excel Help
    Replies: 0
    Last Post: 05-19-2013, 09:30 AM
  5. Replies: 1
    Last Post: 05-20-2012, 12:23 PM

Posting Permissions

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