Vlookup formulas

[pincivma]

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)

 

[Marcelo Branco]

To get rid of the formulas maybe...

Select the cells with formulas and run this macro

Sub aTest()
With Selection
  .Value = .Value
End With
End Sub

M.

 

[steve the fish]

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.

 

[pincivma]

To get rid of the formulas maybe...

Select the cells with formulas and run this macro

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??

 

[Marcelo Branco]

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

M.

 

[pincivma]

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.