Macro Button to Change Lookup Values

[dcliffo]

I have a spreadsheet that utilizes vlookup in another spreadsheet to update specific values for each quarter during the current year. I want to incorporate a button that changes all of these values (looks up values from different columns) depending on whether I want to view "proposed" values or "approved" values. I know I can record a macro and capture updating each of these manually, but is there an easier way of finding and replacing values within a formula for a particular column of data? Thanks for the help in advance.

 

[Perpa]

I have a spreadsheet that utilizes vlookup in another spreadsheet to update specific values for each quarter during the current year. I want to incorporate a button that changes all of these values (looks up values from different columns) depending on whether I want to view "proposed" values or "approved" values. I know I can record a macro and capture updating each of these manually, but is there an easier way of finding and replacing values within a formula for a particular column of data? Thanks for the help in advance.

dcliffo,
This is a 'brute force' method to replace each VLOOKUP formula...but it works!
You didn't post a sample or your data so we didn't have a range for either the 'Proposed' data
or the 'Approved' data. The example below uses an 'Inputbox' and 'Select Case'.
If neither case is selected, you are prompted to 'try again'.
Perpa

Sub ReplaceMyVlookups()	
TryAgain:	
    myStatus= Inputbox("Input '1' for 'Proposed', '2' for 'Approved'")	
	
            Select Case myStatus	
                    Case 1 'Proposed	
	      'Enter your formulae similar to the following for each VLOOKUP
	      'where 'rangename1' is a defined range name...OR actual range, ie. K15:P25
                        Range("C5").value="=VLOOKUP(B5,K15:P25,3,FALSE)"    'Change to suit	
	        Range("C6").value="=VLOOKUP(B6,K15:P25,4,FALSE)"
	      '  Range(.....etc...etc
                    Case 2   'Approved	
	        'where 'rangename2' is a defined range name...OR actual range, ie. K27:P37
                        Range("C5").value="=VLOOKUP(B5,K27:P37,3,FALSE)"    'Change to suit	
	        Range("C6").value="=VLOOKUP(B6,K27:P37,4,FALSE)"
	       ' Range(.....etc...etc
	    Case else
	         Msgbox("You didn't select '1' or '2'...try again")
	         goto TryAgain
            End Select	
	
End Sub

 

[dcliffo]

Thank you for your reply. This was posted quite a while back and since then I just came up with the following: I used a drop-down box with selection of either Proposed or Approved. I used a vlookup which if this cell matched Proposed, then it looked up a different column value then if the selection was Approved. It works...Thanks for the reply.