replace function

[rodney_malod]

hi, ive got a matrix on a spreadsheet. it features the part numbers for components. i have a list with the old numbers and the new numbers for these kits. how do i do a replace function for a alrge number of different kit numbers, so the old codes in the matrix are relpaced with the new ones?

[mdmackillop]

Have a look at the attached.

Sub Replace()
Dim RepList()
ReDim RepList(Range("List").Rows.Count + 1, 2)
RepList = Range("List").Value
For i = 1 To UBound(RepList)
Range("Matrix").Replace What:=RepList(i, 1), Replacement:=RepList(i, 2), LookAt:=xlWhole
Next
End Sub

[rodney_malod]

hmm, i see where its coming from but cant quite apply it for some reason.

Sub Replace()
Dim RepList()
ReDim RepList(Range("a6:a78").Rows.Count + 1, 2)
RepList = Range("list").Value
For i = 1 To UBound(RepList)
Range("e6:e53").Replace What:=RepList(i, 1), Replacement:=RepList(i, 2), LookAt:=xlWhole
Next
End Sub

[mdmackillop]

The one thing that comes to mind, is that I thought in writing this, that the array should be base 0, however that didn't work in my example so I changed it as shown.

Try changing to


Sub Replace()
Dim RepList()
ReDim RepList(Range("a6:a78").Rows.Count, 2)
RepList = Range("list").Value
For i = 0 To UBound(RepList)
Range("e6:e53").Replace What:=RepList(i, 0), Replacement:=RepList(i, 1), LookAt:=xlWhole
Next
End Sub

[skulakowski]

the webcam URL is a view of the Grampian Mountains in Perthshire. See http://www.bbc.co.uk/webcams/ukcams.shtml for the BBC's map of UK webcams.

[rodney_malod]

400 error, should i try a vlookup?

[mdmackillop]

Can you attach your spreadsheet?

[mdmackillop]

I see a problem. Your "list" has only one column of data. I assumed 2 columns, old and new items. In my solution, when an old item is found in the matrix, it will be replace with new. How are you relating your old and new items?

[rodney_malod]

i have a list with two columns, old code and new code

[mdmackillop]

Assuming your data is in A6 to B78




Sub Replace()

Dim RepList()
ReDim RepList(Range("A6:B78").Rows.Count + 1, 2)
RepList = Range("A6:B78").Value
For i = 1 To UBound(RepList)
Range("E6:E53").Replace What:=RepList(i, 1), Replacement:=RepList(i, 2), LookAt:=xlWhole
Next
End Sub