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

 

[Aladin Akyurek]

Care to post 5 rows from Product and specify the desired results on Invoice, based on the sample?

 

[AlanAnderson]

Hi Aladin,
My example is very simple:

Product sheet
P1 Y 11
P2 N 12
P3 N 13
P4 Y 14
P5 N 15

The Invoice sheet starts with only col A filled in:

P1 Y 11
P3 N 13
P5 N 15
P4 Y 14
P2 N 12

I know this is VERY basic but I've deliberately built up a very simple example so that it is easier for me to see how the solution to this can be applied to the real case.

Thanks for the interest. I do appreciate it.

Regards,

Alan

 

[snoopyhr]

See if this helps, to me it looks like a simple match index . may be I am missing something...

Excel Workbook
	D	E	F
21	Data
22	Product	Accept	Qty
23	P1	Y	11
24	P2	N	12
25	P3	N	13
26	P4	Y	14
27	P5	N	15
28
29	Extracted
30	Product	Accept	Qty
31	P1	Y	11
32	P5	N	15
33	P3	N	13
34	P4	Y	14
35	P2	N	12
Sheet1

#VALUE!

 

[AlanAnderson]

Hi Snoopy,

Thank you for your help. Unfortunately the problem is a great deal more complex than the simple example I quoted. For simplicity sake I restricted example to only 3 columns and matched them against an identical col in the Invoice page. The reality is that the invoices will have a variety of codes input and depending on circumstances many calculations and look ups will need to be done.

Firstly it has to be done in VBA and not on the sheets, secondly I have a nasty feeling that I am either going to have to do plenty of loops within loops or probably use arrays.
Thank you for your suggestion.

Regards,

Alan

 

[AlanAnderson]

Bump

 

[AlanAnderson]

Please click one of the Quick Reply icons in the posts above to activate Quick Reply.

 

[Peter_SSs]

Your example looked like a simple INDEX/MATCH or VLOOKUP should do it. Your description at the bottom of your first post also just described 3 columns where the data had to be retrieved based on the first column.

You now say that the problem is much more complex. My feeling is that potential helpers are, like me, wondering just what your data is like and what you are trying to do with it. Without that information it is hard for anybody to help.

So what about making up a bit more realistic sample and post some sample data, expected results and description in relation to that data/results?

Consider using one of the 3 methods in my signature block for posting sample data so that the layout is clear.

 

[AlanAnderson]

Hi Peter,
I appreciate the point you are making - it does make sense.

However my problem is that I have written a procedure that, while it has worked every step of the way using my clumsy code, has now got too big to compile.

I really can't expect anyone on this forum to plough through this 64K of code.

So my purpose of writing this really simplistic example is that hopefully, if someone shows me how to rather do this as an array I can then apply this "lesson" to correcting the larger procedure.

Does this make sense?

Regards and thanks for the interest.

Alan

 

[Rasm]

This code example will place the values into an array (1st piece of code) - so run the code - then on the code window select 'View' then select Locals Window - now you can use the nodes to see what the array values are. The advantage of arrays is that you dont have the range limitation of 254 characters.

2nd code
So I may have an array of 600 Columns and 10K rows - The worksheetfunction.transpose will quickly place my array into my range string (Astr) in 2nd example. Not sure if this helps you - if you want all arrays to start with the 1 element (default is zero) - the in the declaration section use

Option base 1 ' This will make all arrays start at one - Excel is inconsistent - some things starts in element zero - others in 1.

Anyway - hope this helps


Range to array

Private Sub CommandButton3_Click()
Dim MyArray() As Variant
MyArray = ActiveSheet.Range("C5:C10", "D5:D10").Value 
End Sub

Array to range - Astr=my range string and SampleID is my array of data

ActiveSheet.Range(Astr).Value = Application.WorksheetFunction.Transpose(SampleID)