VBA to replace HsGetValue with value

[Thomazz]

Hello all,

I'm on the look-out for something which I think will be relatively easy. I'm looking for a macro/VBA-code that

• will cycle through every sheet of a workbook
• which simply replaces cells that have a formula that includes a HsGetValue with their value (basically as if you would be copy/paste values the cell).

I have Excels built based on SmartView with HsGetValue formulas. However, when I mail this to people that don't have the SmartView set-up, the cells with HsGetValue default to N/A. I want to transform those to their values. However, I want to keep normal formulas (like totals, index/match, ...) that don't have HsGetValue so the user can see how the document is built up.

Whoever that can help me, would save me a bunch of work.

 

[Fluff]

How about

Sub Thomazz()
   Dim Ws As Worksheet
   Dim Rng As Range, Cl As Range
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Rng = Ws.UsedRange.SpecialCells(xlFormulas)
      On Error GoTo 0
      If Not Rng Is Nothing Then
         For Each Cl In Rng
            If InStr(1, Cl.Formula, "hsgetvalue", vbTextCompare) > 0 Then Cl.Value = Cl.Value
         Next Cl
      End If
   Next Ws
End Sub

 

[Thomazz]

How about

Sub Thomazz()
   Dim Ws As Worksheet
   Dim Rng As Range, Cl As Range
  
   For Each Ws In Worksheets
      On Error Resume Next
      Set Rng = Ws.UsedRange.SpecialCells(xlFormulas)
      On Error GoTo 0
      If Not Rng Is Nothing Then
         For Each Cl In Rng
            If InStr(1, Cl.Formula, "hsgetvalue", vbTextCompare) > 0 Then Cl.Value = Cl.Value
         Next Cl
      End If
   Next Ws
End Sub

Just tested it, and seems to work absolutely great !

Small additional question : how would this code look if I only want this on a range I selected myself as opposed to the full workbook ?

 

[Fluff]

How about

Sub Thomazz()
   Dim Rng As Range, Cl As Range
   
   On Error Resume Next
   Set Rng = Selection.SpecialCells(xlFormulas)
   On Error GoTo 0
   If Not Rng Is Nothing Then
      For Each Cl In Rng
         If InStr(1, Cl.Formula, "hsgetvalue", vbTextCompare) > 0 Then Cl.Value = Cl.Value
      Next Cl
   End If
End Sub

 

[Thomazz]

Works like a charm ! Many thanks !

 

[Fluff]

You're welcome & thanks for the feedback.