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!!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

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