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
----------------------
Can you please help me?
Many thanks,
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
Code:
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 = SectorsCan you please help me?
Many thanks,
Last edited by a moderator:
