Results 1 to 6 of 6

Thread: Vlookup formulas
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup formulas

    Hi again

    I have over 5,000 vlookup formulas in a spread sheet that is slowing down doing tasks. I thought of taking out the equal sign on all 5,000 of them and then record a macro while I put back in all the equal signs on all 5,000 formulas. This will create all the formulas in the recorded macro. Then my next step is this. I can then do a copy > paste special > Values macro and get rid of the Vlookup formulas. I can refresh the sheet by running the macro and only values will now appear instead of the formulas. My problem is that I can do it but I have to go through all of the Vlookup formulas one by one, take out all the equal signs first, then record a macro while I reinstates all the equal signs back in again and then do a copy > paste special > Values on all the Vlookup formulas. This method will take me forever to do. Is there a macro that will tale out the equal sign and the another macro to put them all in again? Here is a sample of only 1 Vlookup formula.


    =VLOOKUP($A12,Sheet2!$A$11:$G$21,5,FALSE)

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,926
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Vlookup formulas

    To get rid of the formulas maybe...

    Select the cells with formulas and run this macro

    Code:
    Sub aTest()
    With Selection
      .Value = .Value
    End With
    End Sub
    M.

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,261
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup formulas

    How about creating a 'values only' workbook that is a replica of the original workbook without formulas. You could use a macro to refresh as and when you need to refresh.

  4. #4
    Board Regular
    Join Date
    Dec 2004
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup formulas

    Quote Originally Posted by Marcelo Branco View Post
    To get rid of the formulas maybe...

    Select the cells with formulas and run this macro

    Code:
    Sub aTest()
    With Selection
      .Value = .Value
    End With
    End Sub
    M.
    Hi again

    Thanks for the macro but I have to reinstate all the VlookUp formulas again since the data changes. I don't think that you understood me. I have to record a macro that puts in all the 5,000 formulas in all those cells. I can do it one at a time by deleting the equal signs and then record a macro by putting all the equal signs back in again. I can call this macro "RerfreshData". This macro puts back all of the VLookUp formulas in all the cells. This method will have all the formulas in the macro called "RefreshData". Then I can use your macro. Get it now??

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,926
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Vlookup formulas

    Sorry, i misunderstood what you need.
    What about Steve's suggestion in post 3?

    M.

  6. #6
    Board Regular
    Join Date
    Dec 2004
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup formulas

    Quote Originally Posted by pincivma View Post
    Hi again

    Thanks for the macro but I have to reinstate all the VlookUp formulas again since the data changes. I don't think that you understood me. I have to record a macro that puts in all the 5,000 formulas in all those cells. I can do it one at a time by deleting the equal signs and then record a macro by putting all the equal signs back in again. I can call this macro "RerfreshData". This macro puts back all of the VLookUp formulas in all the cells. This method will have all the formulas in the macro called "RefreshData". Then I can use your macro. Get it now??

    Thank you both. I figured out how to do it.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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