I am using a Find and Replace, simple VBA code as below:
Sub MultiFindNReplace() 'Update 20140722 Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value Next Application.ScreenUpdating = True End Sub
However, the dataset is over 685,000 values large and this keeps crashing my excel document. I've tried putting in error catches and other methods to find and replace.
Both columns are in sheet2. Column to be replaced is column 10, column and replacement are in columns 17 and 18.
The data looks like this
Time With Bank Time With Bank Transformed Score
999 999 5 5 1 4 27 2 4 3 3 2 ... ... ... 999 207 1.3
Looking To replace all of the first column which has 686950 entries with the transformed score (column 3) of each of column 2, which has the time with bank for 80 different monthly entries.