with 100 000 columns of data it is questionable as to the best solution.
You could copy al the data into vba modify it and paste it back this is normally the fastest method.
However if you keep all the data in excel you can use find an replace which might speed things up.
Sub Macro1()
On Error Resume Next
For Count = 1 To Selection.SpecialCells(xlCellTypeLastCell).Row
searchstring = Range("A" & Count).Value
Range("B1:B" & Selection.SpecialCells(xlCellTypeLastCell).Row).Select
Selection.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If ActiveCell.Value <> searchstring Then GoTo 100
String2 = ActiveCell.Offset(0, -1).Value
Range("A1:A" & Selection.SpecialCells(xlCellTypeLastCell).Row).Select
Selection.Replace What:=searchstring, Replacement:=String2, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
100 Next
End Sub
Bookmarks