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:
Hope that one of you will be my savior .
Thanks!!
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!!