macro to convert formulas to values for data not consecutative

[SHough]

I have an Excel workbook with many tabs and each tab contains a similar formula in every 5th column. For these tabs I want to convert this particular formula for a very specific range on each tab from a formula to a value.

I would like to create a macro that finds every cell that contains the letters "Assumptions!$" (omit the quotes) and replace that cell with its value, then continue on to find the next cell that meets this criteria. I want this macro to only run for the range actually selected. I need to limit this to only the range selected because I have hidden rows below any particular range that need to retain this formula.

I can not use the paste special command because the cells are not contiguous.

Any ideas out here?

 

[GlennUK]

At a guess:

For Each c in Selection
   InStr(1, c.Formula, "Assumptions!$") > 0 Then
      c.Value=c.Value
   End If
Next

 

[SHough]

Thank you!!

I needed to add an 'If' parameter to the front end of the InStr...:

Sub Turn_Off_PY_to_CY()
For Each c In Selection
If InStr(1, c.Formula, "Assumptions!$") > 0 Then
c.Value = c.Value
End If
Next
End Sub

But it works perfectly !!

Saving me hours of work. My own IT department did not come up with the answer.

SHough

 

[GlennUK]

My pleasure. I'm glad you spotted my mistake ... I'd guessed the code in my head, so it wasn't really checked.

It sounds like you're saving a lot of time by using the macro ... that's great!