Find and Replace contents via lists.

[RedMagesHat]

Hi all, I was hoping someone who be able to help me with a find and replace macro I'm working on.

The idea is that I will have one long list of data for lots of items listed by code and name, and then with a qty value associated with them.

Then I'll have another list of inputs, that will have an item code, and then a new qty. I've taken a screen shot of what I mean bellow.

So what I want to do is take the list on the left, the "inputs" and using the Numeric Code as a reference, I want to search through the list on the right, the "Database" and when I match the Input code to a Code in the Database, it will replace the Qty in the Database with the Qty in the Inputs.

The code I'm trying looks like:

Sub Mysearch()

NumInRows = Range("A" & Rows.Count).End(xlUp).Row 'Counts the number of rows in the input section
NumDBRows = Range("H" & Rows.Count).End(xlUp).Row 'Counts the number of rows in the Data Base


Dim i As Long 'declare for loop counter
Dim j As Long 'declare second loop counter
Dim RefCode As Long 'declare variable to store code being found
Dim NewQty As Long 'decalre variable to store qty to replace


For i = 2 To NumInRows 'Loop to run through the reference list
    RefCode = Cell(i, 1).Value 'set find value to comm code in current row column 1
    NewQty = Cell(i, 2).Value 'set replace qty to qty in current row column 2
    For j = 2 To NumCBRows 'Loop to run through the database list
        If Cell(j, 8).Value = RefCode Then 'checks if the cell in row j column 8 is the same as the reference code
        Cell(j, 11) = NewQty 'if so then replaces the qty in row j column 10 with the new qty
        End If
        Next j 'move to next j row and keep checking
    Next i 'move to next i row to get new refernce code and qty


End Sub

But anytime I try to run it I get the following error:

Any help would be very appreciated. I'm new to VBA and excel macros so I'm kinda just groping around on this. Thanks.

 

[RedMagesHat]

Ok so I found my problem (the function is Cells() not Cell(), duh).

So after some debugging I have my code working as follows:

Sub Mysearch()

NumInRows = Range("A" & Rows.Count).End(xlUp).Row 'Counts the number of rows in the input section
NumDBRows = Range("E" & Rows.Count).End(xlUp).Row 'Counts the number of rows in the Data Base


Dim i As Long 'declare for loop counter
Dim j As Long 'declare second loop counter
Dim RefCode As String 'declare variable to store code being found
Dim NewQty As String 'decalre variable to store qty to replace


For i = 3 To NumInRows 'Loop to run through the reference list
    Cells(i, 1).Select
    RefCode = Cells(i, 1).Value 'set find value to comm code in current row column 1
    NewQty = Cells(i, 2).Value 'set replace qty to qty in current row column 2
    ' MsgBox "RefCode = " & RefCode & " NewQty = " & NewQty
    For j = 2 To NumDBRows 'Loop to run through the database list
        Cells(j, 5).Select
        ' MsgBox "Being Reffed = " & Cells(j, 5).Value
        If Cells(j, 5).Value = RefCode Then 'checks if the cell in row j column 8 is the same as the reference code
        Cells(j, 7) = NewQty 'if so then replaces the qty in row j column 10 with the new qty
        End If
    Next j 'move to next j row and keep checking
Next i 'move to next i row to get new refernce code and qty


End Sub

Now I'm going to try and take it so that my Database and my Inputs are on different sheets. Anyone have any advice on how to accomplish this?