VBA copy and replace help

[kylek04]

I'm a novice with VBA and was hoping for some help on this. I have a sheet that has various formulas throughout. It includes index formulas that pull information from other workbooks but also simple sum formulas. What I'd like the macro to do is cycle through each cell in the worksheet and if it contains an index formula to just simply copy and paste the value that is in that cell. Below is just a small sample of the worksheet contents.

The worksheet name is 'Template'
Any help would be greatly appreciated.

 

[Kaper]

Try this code:

Sub test()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
  If cell.Formula Like "=INDEX(*" Then cell.Value = cell.Value
Next cell
End Sub

If it works but is too slow, we could work on making it a bit faster by switching off calculation and screen updating before run and switch it back on after

 

[mse330]

This is another method of which should be faster

Sub test()

Dim Rg As Range, Adrs$
Set Rg = Cells.Find("=INDEX", lookat:=xlPart, LookIn:=xlFormulas)

If Not Rg Is Nothing Then
    Adrs = Rg.Address
    Rg = Rg.Value
    Do
        Set Rg = Cells.Find("=INDEX", lookat:=xlPart, LookIn:=xlFormulas, after:=Rg)
        If Not Rg Is Nothing Then Rg = Rg.Value Else Exit Sub
    Loop While Adrs <> Rg.Address
End If

End Sub

 

[Kaper]

The speed would depend (between others) on what percentage of all cells have the =INDEX... formula. But as it's the tercentage of all cells in UsedRange, then I expect FIND will be indeed much faster. Using Find one can do it also with less complicated loop:

Sub test()
Dim Rg As Range
Set Rg = Cells.Find("=INDEX", lookat:=xlPart, LookIn:=xlFormulas)
While Not Rg Is Nothing
  Rg.Value = Rg.Value
  Set Rg = Cells.FindNext
Wend
End Sub

Note that we are changing cell content so it will be never found again. And it allows us to use just FindNext without any checking for infinite looping.

or to have a message after the work is finished with a small summary a bit longer code:

Sub test()
Dim Rg As Range, counter As Long
Set Rg = Cells.Find("=INDEX", lookat:=xlPart, LookIn:=xlFormulas)
While Not Rg Is Nothing
  Rg.Value = Rg.Value
  counter = counter + 1
  Set Rg = Cells.FindNext
Wend
MsgBox "Changed " & counter & " formulas =INDEX( ... into values", vbInformation + vbOKOnly
End Sub

 

[Alex Blakenburg]

Here is an array option, that I would expect would be quicker again.

Sub ConvertIndexToValue()

    Dim rng As Range
    Dim arrForm As Variant, arrVal As Variant
    Dim i As Long, j As Long
   
    Set rng = ActiveSheet.UsedRange
   
    arrForm = rng.Formula
    arrVal = rng.Value
   
    For i = 1 To UBound(arrForm)
        For j = 1 To UBound(arrForm, 2)
            If InStr(1, arrForm(i, j), "INDEX", vbTextCompare) Then
                arrForm(i, j) = arrVal(i, j)
            End If
        Next j
    Next i
    rng.Formula = arrForm

End Sub

 

[Kaper]

It's only a comment, as all proposed methods do the job:

Indeed, array approach (as quite often if there is many single cell operations) is the quickest.
The speed of given method would depend (between others) on what percentage of all cells have the =INDEX... formula.
While Alex was writing I did a small test on some 100x10000 cells range and the code form post #2 is quicker (at my computer - confoguration could also be important) already for about 5% of formulas from post #3. The higher the percentage, the bigger the speed difference.

And one more comment: If we decide to use .Find method, looping could be also done a bit more elegant way (but not necesserily leading to quicker execution):

Sub test()
Dim Rg As Range
Set Rg = Cells.Find("=INDEX", lookat:=xlPart, LookIn:=xlFormulas)
While Not Rg Is Nothing
  Rg.Value = Rg.Value
  Set Rg = Cells.FindNext
Wend
End Sub

Note that we are changing cell content, so it will be never found again. This allows us to use just FindNext without any checking for possible infinite looping.

But let me get back to second paragraph: if there shall be changes in many cells Array approach either to range.value or to range.formula will result in huge savings of execution time.