Question: Replace Range with arrays

bgoo1337

New Member
Joined
Mar 13, 2016
Messages
3
Hi all,

I am trying to build an replace function to replace certain values in a range, however the loop is taking a pretty long time (20 min).

The range(C2:C60000) to be replaced contains cells like this:
Object1 = Object2 + Object3
Object2 = Object4

Then, I have this, to replace the 'Objects' with values
Column A || Column B
Object1 || 12
Object2 || 7
Object3 || 5
Object4 || 7

The outcome must be this, as the objects have been replaced with values accordingly:
12 = 7 + 5
7 = 7

The code I have written thusfar is this:

Code:
Application.ScreenUpdating = False

Dim Oldvalue As Variant
Dim Newvalue As Variant

Oldvalue = Sheets("1").Range("A2:A42178")
Newvalue = Sheets("1").Range("B2:B42178")

For i = 1 To 42178 'I have 42178 objects of 60000 with values in this example

If aValueNew(i, 1) <> 0 Then 'save time i Think

x = aValueOld(i, 1)
y = aValueNew(i, 1)

ActiveSheet.Range("C2:C60000").Replace What:=x, Replacement:=y, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

End If
Next i
Application.ScreenUpdating = True

Hope that one of you will be my savior :).

Thanks!!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
60,000 rows, 40,000 replacements, that over 2,400,000,000 operations.

I'd just be happy that it wasn't crashing.

Have you tried adding Application.ScreenUpdating = False at the start of your Sub (= True at the end)?

If there are any down-stream calculations on those cells, also set Application.Calculation to xlManual (xlAutomatic at the end)
 
Last edited:
Upvote 0
See if this is any faster...
Code:
Sub ReplaceObjectsWithNumericalEquivalents()
  Dim X As Long, Z As Long, Data As Variant, Objects As Variant, Obj As Object
  Data = Range("C2", Cells(Rows.Count, "C").End(xlUp))
  Objects = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  For X = 1 To UBound(Data)
    For Z = 1 To UBound(Objects)
      Data(X, 1) = Replace(Data(X, 1), Objects(Z, 1), Objects(Z, 2))
    Next
  Next
  Range("C2").Resize(UBound(Data)) = Data
End Sub

Note: You did not say, so I assumed your object replacement table (Columns A and B) started on Row 2 like the data table (Column C) did.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top