Replace column values based on a list

Hi All,

I am working on a project which has many parts to the code. I have the following code that works and runs as needed, here is what I am doing here.
This code uses two tabs, "Old Data" and "New Data"

Old Data tab is my present data and consists of for this sample 2 columns, ID numbers and holds numbers 1 to 9, and is under column A and Item under column B with a list of items.
New Data tab is my new data list and consists of for this sample 2 columns, ID numbers and also holds numbers 1 to 9, and is under column A and Item under column B has a list of new updated items.

So, if old data tab col A has a 1 as the ID and Item as Apple, then match new data tab col A which has a 1 as the ID and item AppleFruit. I want Apple Fruit to replace the 1 on Old Data tab so I have AppleFruit as ID and Apple as the Item.

My question is, instead of another tab holding the new data, can I have an array holding this new data list within the code itself, or some other way to do this, the list will be small, maybe 20 items and will be added to over a period of time to reach the 20 items.

I hope my description is clear enough

Sub replaceData()

Dim i As Integer, j As Integer
Dim sheetOne As Worksheet, sheetTwo As Worksheet, myWb As Workbook
Dim myData As String, myId As String


'On Consolidate sheet, replace PO Number with Department number
    Set myWb = Excel.ActiveWorkbook 'These three lines set your workbook/sheet variables.
    Set sheetOne = myWb.Worksheets("Old Data")
    Set sheetTwo = myWb.Worksheets("New Data")
    
    For i = 1 To sheetTwo.UsedRange.Rows.Count 'This loops through the rows on your second sheet.
       myId = sheetTwo.Cells(i, 1).Value 'This assigns the value for your id and the data on your second sheet.
       myData = sheetTwo.Cells(i, 2).Value
       For j = 1 To sheetOne.UsedRange.Rows.Count 'This loops through the rows on your first sheet.
          If sheetOne.Cells(j, 1).Value = myId Then 'This checks each row for a matching id value.
             sheetOne.Cells(j, 1).Value = myData 'This replaces that id with the data we got from the second sheet.
          End If
       Next j
   Next i

End Sub

Any help will be appreciated on how to updated the code to do what I want!

Hi,

I just thought of this, can a select case work with a list of items??

Thanks for the reply mehmetcik!

I guess I don't follow you, I have two values, ID and items. Are you using the MyArray(1) for the ID as per my example?

Thanks mehmetcik, I appreciate the reply!

I think I understand what you are saying. I did a little goggling and found simple examples. Could I perhaps have two set of a arrays.

Maybe one as "Oldarray" with present values, then another one with values I want to change the old values too, "NewArray"

How do I modify my present code to add in the arrays?