Using Arrays instead of this code

[AlanAnderson]

Hi,
I have never used arrays before and what I've read either scares or confuses me.

What I need to know is would the use of an array be faster and/or easier than the code below (Bit in question is between XXXX's)

This is just a very simple example to illustrate what is a far more complex issue I need to solve. If I can see how this simple examplwe would work I could then apply it to my real problem.

How would the following code translate into an effective array?

Sub Test2()
    Dim vProductCode As String
    Dim vTaxYN As String
    Dim vPrice As Double
    Dim wsProd As Worksheet
    Dim wsInv As Worksheet
    Dim FinalRowProd As Long
    Dim FinalRowInv As Long
 
    Set wsProd = Worksheets("Product")
    Set wsInv = Worksheets("Invoice")
 
    FinalRowProd = wsProd.Cells(Rows.Count, 1).End(xlUp).Row
    FinalRowInv = wsInv.Cells(Rows.Count, 1).End(xlUp).Row
    'XXXXXXXXXXX
        For i = 1 To FinalRowInv
        vProductCode = wsInv.Cells(i, 1)
            For j = 1 To FinalRowProd
                If vProductCode = wsInv.Cells(j, 1) Then
                    wsInv.Cells(i, 2) = wsProd.Cells(j, 2)
                    wsInv.Cells(i, 3) = wsProd.Cells(j, 3)
                End If
            Next j
        Next i
    'XXXXXXXXXXX
End Sub

There are just 2 worksheets in this example. "Product" - in col A is product code, Col B a "Y" or "N" and Col C a value.
In sheet "Invoice I have Col A with product code and need to fill in Cols B and C with relevant data from sheet "Product"

I hope this makes sense.

BTW I use 2003 but am trying to keep it compatible with 2007/2010

Thanks for the ongoing help from this forum

Alan

 

[AlanAnderson]

Hi Peter,

That result is EXACTLY what I need. Once I get that the rest is relatively easy.

I will work on this tonight.

Thanks a million times for your help. I appreciate it so much.
Maybe I should refer to you as Saint Peter

Thanks

Alan