Arrays - starting out with a 2 dimensional array

[Oberon70]

Hi,

I have a lot of help and recommendations on arrays, so I decided to try to learn the basic 2 dimensional array 1st.

I am taking information that is span across two worksheets in the same workbook

Sub AddInvDetails()

Dim InvArray(1 To 9, 1 To 2)

InvArray(1, 1) = "Invoice Date"
InvArray(1, 2) = SrcData.Range("C3") 'Invoice Date

InvArray(2, 1) = "Recovery Agent"
InvArray(2, 2) = SrcData.Range("C9") 'Name of Agent

InvArray(3, 1) = "Portfolio Code"
InvArray(3, 2) = SrcData.Range("C30") 'Portfolio Code

InvArray(4, 1) = "Invoice Number"
InvArray(4, 2) = SrcData.Range("C37") 'Invoice Number

InvArray(5, 1) = "Total Gross Amount"

InvArray(5, 2) = SrcTaxInvoice.Range("K35") 'Total Gross Amount

InvArray(6, 1) = "Net Commission"
InvArray(6, 2) = SrcTaxInvoice.Range("X30") ' Net Commission

InvArray(7, 1) = "GST Amount"
InvArray(1, 7) = SrcTaxInvoice.Range("X33") ' GST Amount

InvArray(8, 1) = "Gross Commission"
InvArray(1, 8) = SrcTaxInvoice.Range("AA34")  'Gross Commission

InvArray(9, 1) = "Net Amount"
InvArray(1, 9) = SrcTaxInvoice.Range("AA34") 'Net Amount paid

End Sub

But I am getting an error

Is something able to advise me what I am doing wrong? that is causing the above error.

I thought maybe it is because the source data is on a merged cell example ='Tax Invoice'!I32:K32, but I tried replacing K35 above with I32:K32, but received the same error.

 

[RoryA]

What are SrcData and SrcTaxInvoice?

 

[Oberon70]

Sorry they are declared globally so that they can be sued in different modules.

Dim SrcSTMT As Workbook, SrcData As Worksheet, SrcTaxInv As Worksheet

 

[offthelip]

Have you set SrcData and SrcTaxinv to anything?? and if it is not done in the sub routine you have posted are you sure the set code has run before you execute this code??

 

[offthelip]

InvArray(7, 1) = "GST Amount"
InvArray(1, 7) = SrcTaxInvoice.Range("X33") ' GST Amount

InvArray(8, 1) = "Gross Commission"
InvArray(1, 8) = SrcTaxInvoice.Range("AA34")  'Gross Commission

InvArray(9, 1) = "Net Amount"
InvArray(1, 9) = SrcTaxInvoice.Range("AA34") 'Net Amount paid

This code looks wrong to me: you are going to get the text "GST Amount" in Array equivalent of G1 and and the amount you have set it to in the equivalent of A7
ditto the next two array assignment. Very confusing to use!!

 

[Alex Blakenburg]

I can't get the same error message but there are a lot of errors in your code.
1) You have dimmed SrcTaxinv but are using SrcTaxInvoice.

2) As @offthelip has pointed out you need something like this:

Set SrcData = Worksheets("Data")        
Set SrcTaxInv = Worksheets("TaxInv")

3) Array Subscript out of range
You were following this pattern:

InvArray(6, 1) = "Net Commission"
InvArray(6, 2) = SrcTaxInvoice.Range("X30") ' Net Commission

Which is consistent with your array dimensioning of

Dim InvArray(1 To 9, 1 To 2)

But this went off the rails with your last 3, which I am sure should have been (7,1) & (7,2), (8,1) & (8,2) andn (9,1) & (9,2)
Your (1,7), (1,8) & (1,9) give you subscript out of range since you have only dimensioned the column reference as 1 to 2.

 

[Oberon70]

I shouldn't try to write something near midnight after working all day.

errors galore.....fixed the last three to (7,2),(8,2) and (9,2)

fixed my variable named and now it works.

 

[RoryA]

fixed my variable named and now it works

 

[Oberon70]

thank you, I will change the above setting.