Using Arrays instead of this code

AlanAnderson · Apr 1, 2011

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?

Code:

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 · Apr 1, 2011

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

AlanAnderson · Apr 1, 2011

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 · Apr 1, 2011

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 · Apr 1, 2011

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 · Apr 1, 2011

AlanAnderson · Apr 1, 2011

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

Peter_SSs · Apr 1, 2011

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 · Apr 1, 2011

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 · Apr 1, 2011

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

Code:

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

Code:

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

Using Arrays instead of this code

AlanAnderson

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

AlanAnderson

Board Regular

snoopyhr

Active Member

AlanAnderson

Board Regular

AlanAnderson

Board Regular

AlanAnderson

Board Regular

Peter_SSs

MrExcel MVP, Moderator

AlanAnderson

Board Regular

Rasm

Well-known Member

Similar threads

Forum statistics

Share this page

Using Arrays instead of this code

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Active Member

Board Regular

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock