Vlookup formulas

pincivma

Board Regular
Joined
Dec 12, 2004
Messages
203
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)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
Sorry, i misunderstood what you need.
What about Steve's suggestion in post 3?

M.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top