macro to paste numeric values for all references in an equation

**chriszoma** · 05-31-2013, 12:47 AM

Say cell a1 has a formula that is =+b4+d8+e10 and b4=8 , d8=10 , e10=5 I want the macro to return =+8+10+5 . I actual want it for all vlookup formulas in the worksheet My worksheet has multiple vlookups in each individual cells example '=VLOOKUP(A4,Advertising!E:R,14,FALSE)+VLOOKUP(A4,Advertising!E:R,15,FALSE)

**:) Sixthsense :)** · 05-31-2013, 12:55 AM

Originally Posted by chriszoma

example '=VLOOKUP(A4,Advertising!E:R,14,FALSE)+VLOOKUP(A4,Advertising!E:R,15,FALSE)

Instead of using like the above, just reference the output columns within array

Formula:

Please Login or Register  to view this content.

Edit: But E to R the number of columns is 14 only and mentioning 15 will result error

**chriszoma** · 05-31-2013, 01:27 AM

My boss does not understand vlookups so she wants me to key in each numeric number for each vlookup within each cell there are hundreds of calculation using vlookups and most cell have two to five vlookups inside the cell's formula. I want to keep the vlookups in tack for me because the input data changes each day amd the vloopups I dont have to key all the number but I want to convert the vlookup in each cell to read for example =+215,000+245,455.75-315,000 not =775,455.75 as paste values do

**:) Sixthsense :)** · 05-31-2013, 01:30 AM

Still I am unable to understand what is your exact requirement is??

**chriszoma** · 05-31-2013, 01:43 AM

Cell B4 has a formula--> =VLOOKUP(A4,Advertising!E:R,14,FALSE)+VLOOKUP(A4,Advertising!E:R,2,FALSE)
=VLOOKUP(A4,Advertising!E:R,14,FALSE) returns 215,0000
=VLOOKUP(A4,Advertising!E:R,2,FALSE) returns 245,455.75
I want a macro to convert B4 to show =+215,000+245,455.75 and all other cell with vlookup calculations
I don't want B4 to read =460,455.75 which is an easy paste special values

Thank so much if anyone can help

**jindon** · 05-31-2013, 02:11 AM

Select the cell(s) that you want to convert, then run the code

Please Login or Register  to view this content.

**chriszoma** · 05-31-2013, 02:51 AM

The Code needs some more work. Only works for the two vlookups in cell with addition or substaction but not multiplication or division. And it won't work for 3 or more vlookups in one cell calculation

**jindon** · 05-31-2013, 03:32 AM

Then upload your file with the formula

You only gave

=VLOOKUP(A4,Advertising!E:R,14,FALSE)+VLOOKUP(A4,Advertising!E:R,2,FALSE)
=VLOOKUP(A4,Advertising!E:R,14,FALSE) returns 215,0000
=VLOOKUP(A4,Advertising!E:R,2,FALSE) returns 245,455.75