CODEWITHSUNDEEP
vbaexcel
Frank
Frank

Reputation: 3

Writing from an array to a range

I am working on a list and doing all the calculations on VBA however when i want to write my list to the predefined range i get nothing. The following is a an example of the code i'm using. I am not posting the actual code because it's long however this example has the same problem.

Option Explicit

Sub readArray()
Dim CoGrade() As Variant
Dim LastRow As Integer
Dim NPSeQuedan() As Variant
Dim SeQuedanRng As Range


'erases information from arrays if there was any
Erase CoGrade

Erase NPSeQuedan

'-------------------------------------------------------------------------
'find the last row on the data i want to read
LastRow = Range("b10000").End(xlUp).Row
'the relevant data starts on row 34
ArrayRows = LastRow - 34 + 1
'redifines the variables with the total numbers of stocks in the portfolio
ReDim CoGrade(ArrayRows, 1)
ReDim NPSeQuedan(ArrayRows, 1)

'reads each relevant number into its proper variable
CoGrade = Range(Cells(34, 2), Cells(LastRow, 2))

'' test
Set SeQuedanRng = Range(Cells(34, 13), Cells(34 + ArrayRows - 1, 
13))
For a = 1 To ArrayRows
    NPSeQuedan(a, 1) = CoGrade(a, 1)
Next
SeQuedanRng.Value = NPSeQuedan
'''
end sub

Upvotes: 0

Views: 70

Answers (2)

John Coleman
John Coleman

Reputation: 51998

Here is another solution (though @SJR 's idea of using 1-dimensional arrays is good). I added various points about your original code in the comments to the code:

Sub readArray()
    Dim CoGrade As Variant 'Don't bother with ()
    Dim LastRow As Long 'Integer risks overflow
    Dim A As Long, ArrayRows As Long 'you use these -- so declare it
    Dim NPSeQuedan As Variant 'etc.
    Dim SeQuedanRng As Range


    'erases information from arrays if there was any
    'Erase CoGrade -- VBA is garbage collected and these have just been declared, so 100% pointless

    'Erase NPSeQuedan

    '-------------------------------------------------------------------------
    'find the last row on the data i want to read
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'why hard-wire in 10000?

    'the relevant data starts on row 34
    ArrayRows = LastRow - 34 + 1
    'redifines the variables with the total numbers of stocks in the portfolio
    'ReDim CoGrade(ArrayRows, 1) -- pointless

    ReDim NPSeQuedan(1 To ArrayRows, 1 To 1) 'this is important for what you are doing

    'reads each relevant number into its proper variable

    CoGrade = Range(Cells(34, 2), Cells(LastRow, 2)).Value

    '' test
    Set SeQuedanRng = Range(Cells(34, 13), Cells(34 + ArrayRows - 1, 13))

    For A = 1 To ArrayRows
        NPSeQuedan(A, 1) = CoGrade(A, 1)
    Next

    SeQuedanRng.Value = NPSeQuedan 'works now!

End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

You can do it like this, which incorporates several of the comments made by John Coleman.

Sub readArray()

Dim CoGrade As Variant
Dim LastRow As Long, ArrayRows as Long, a as Long
Dim NPSeQuedan() As Variant
Dim SeQuedanRng As Range

'find the last row on the data i want to read
LastRow = Range("b10000").End(xlUp).Row
'the relevant data starts on row 34
ArrayRows = LastRow - 34 + 1
'redifines the variables with the total numbers of stocks in the portfolio
ReDim NPSeQuedan(1 To ArrayRows)

'reads each relevant number into its proper variable
CoGrade = Range(Cells(34, 2), Cells(LastRow, 2))
Set SeQuedanRng = Range(Cells(34, 13), Cells(34 + ArrayRows - 1, 13))

For a = 1 To ArrayRows
    NPSeQuedan(a) = CoGrade(a, 1)
Next

SeQuedanRng.Value = Application.Transpose(NPSeQuedan)

End Sub

Upvotes: 1

Related Questions