2D array

[newbie777]

Hi Guys!

Again i need your help!

I have filled a 2D array with data that i need to populate to a range. The issue is, one D is populated in a row and the other is to a column. Typically i need them to be populated to two adjacent column. The first D has sector names and the 2nd D has Values. I need the first sector name and then the first value beside it.

1st Sector 1st value
2nd Sector 2nd value

.
.
.
.



The code as below

Sub weight_upload()
Dim Sht As Worksheet: Set Sht = Sheets("Main")
Dim sht1 As Worksheet: Set sht1 = Sheets("SDC")
Dim sht2 As Worksheet: Set sht2 = Sheets("Uploads")
Dim sht3 As Worksheet: Set sht3 = Sheets("Sector Mapping")
Dim sht4 As Worksheet: Set sht4 = Sheets("BDS")
Dim sht5 As Worksheet: Set sht5 = Sheets("PSE")
Dim sht6 As Worksheet: Set sht6 = Sheets("All Sectors")
Dim sht7 As Worksheet: Set sht7 = Sheets("ASDC")
Dim LastR1 As Integer: LastR1 = sht1.Cells(Cells.Rows.Count, "M").End(xlUp).Row
Dim LastR2 As Integer:  LastR2 = sht2.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim LastR3 As Integer:  LastR3 = sht3.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim LastR4 As Integer:  LastR4 = sht4.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim LastR5 As Integer:  LastR5 = sht5.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim LastR6 As Integer:  LastR6 = sht6.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim LastR7 As Integer:  LastR7 = sht7.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Dim R, R1, R2 As Range
Dim RGI_Weights, RGC_Weights As Variant
Dim Headers1(), Headers2() As String
Dim L, I, K, M, N, O As Integer: N = sht3.Cells(Cells.Rows.Count, "F").End(xlUp).Row: M = sht1.Cells(Cells.Rows.Count, "A").End(xlUp).Row: K = 1: L = 1: M = 0
Dim TMC, Adj_weights, AWF, Sector_MC, Sector_Cap, Sectors() As Variant




' calculating unchanged variables


For Each R In sht3.Range("F2:F" & sht3.Cells(Cells.Rows.Count, "F").End(xlUp).Row)


ReDim Preserve Sectors(0 To N, 0 To N)


  
    Sectors(K, 0) = R.Value
   
    For Each R1 In sht1.Range("m2:m" & LastR1)
    Set R2 = sht3.Range("A2:A" & LastR3).Find(R1.Value, LookIn:=xlValues, Lookat:=xlWhole)
    If R2 Is Nothing Then
        MsgBox (R1.Value & " cannot be found in the Sector Mapping sheet" & vbNewLine & "Please add the stock to the Sector mapping sheet and Run the Macro again"): Exit Sub
        End If
    Select Case Sectors(K, 0)
        Case Is = R2.Offset(0, 1).Value
     
           Sectors(0, L) = Sectors(0, L) + (R1.Offset(0, 11).Value * R1.Offset(0, 12).Value * R1.Offset(0, 13).Value * R1.Offset(0, 15).Value)
    
    End Select




Next R1
K = K + 1
L = L + 1
Next R


'populating the array


sht1.Range("AR2:AS6").Resize(N, N).Value = Sectors

----------------------

Can you please help me?

Many thanks,

 

[Mentor82]

Hi,
Youneed to have the same range set in worksheet as in the array(the same no of rows and columns) in order to return values from the array directly to the worksheet.
You should correct the following lines as follows and it shoud work:

ReDim Preserve Sectors(1 To N, 1 To N)
sht1.Range(sht1.Range("AR2"),sht1.Range("AR2").Offset(N-1,N-1)).value=Sectors

Regards,
Sebastian

 

[jolivanes]

Just for your information.

Dim L, I, K, M, N, O As Integer

The way you have it here means that L, I, K, M and N are declared as variables, not as you probaly assume as Integers.
If they all need to be declared as Integers, it needs to be as follows

Dim L As Integer, I As Integer, K As Integer and so forth.

 

[newbie777]

Just for your information.

Dim L, I, K, M, N, O As Integer

The way you have it here means that L, I, K, M and N are declared as variables, not as you probaly assume as Integers.
If they all need to be declared as Integers, it needs to be as follows

Dim L As Integer, I As Integer, K As Integer and so forth.

So you mean they are declared as Variants? You sure?

Many thanks to you

 

[newbie777]

I changed the lines but i still get it as below:

Agriculture	400232590280.59	401596794937.15	2433987054672.25	108172375291.64	974754290163.08
Alternatives
Energy
Forest Products
Metal & Mining Any other Ideas?

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

 

[Mentor82]

Change also in three lines in sectors array 0 to 1 and let me know the result. By the way, yiu have to declare each variae separately as integer, long or whatever, because otherwise it's a variant instead.

 

[Mentor82]

Sorry there are four places, two lines of each: change sectors(K,0) to sectors(K,1) and sectors (O,L) to sectors(1,L)

 

[newbie777]

Hi mentor, Same result. It didnt work.

Anyway I changed the code with a workaround.

Many thanks for your help though