Range Value only certain formulas in a range?

neov

Board Regular
Joined
Oct 3, 2003
Messages
66
I have a range of data that comes from Vlookups....in column A are the department/account numbers, columns b - k are totals for Jan - Dec, and each department has subsets, for example, rows 2-5 are payroll related, row 6 is a subtotal for that subset, then rows 7-10 are another subset, etc etc...

What I'd like to do is have a macro that performs a copy/paste special/value function ONLY on the cells that have a vlookup, leaving the sum's that make up the subtotals for each month intact...

Is this possible?

Thanks in advance.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

Code:
Sub xxx()
Dim ce As Range
For Each ce In Range("B2:K20")
If Left(ce.Formula, 8) = "=VLOOKUP" Then ce = ce.Value
Next ce
End Sub
 

Forum statistics

Threads
1,147,845
Messages
5,743,513
Members
423,801
Latest member
paulj4177

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
Top