Error in assigning Array value

[smallxyz]

Hello,

I want to record the values of income and their respective month and day values into 3 arrays, i.e. income(), income_m() and income_d(). But there is an error message of "mismatching". I don't know what it means exactly and would like to seek your help.

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

Col B: date (in the format of YYYY/MM/DD)
Col D: amount of income


The outlook of my worksheet looks like this ( only col B and D are displayed below )

	B	D
1	Date	Income
2	2015/1/14
3	2015/2/23
4	2015/3/17	10,000
5	2015/4/23	20,000
6	2015/5/29	15,000

<tbody>
</tbody>

My code:

If Application.WorksheetFunction.CountA(Range("D:D")) > 1 Then
    
        i = Range("D1").End(xlDown).Row
        j = 1
        
        Do While i <> Rows.Count + 1
        
[COLOR=#ff0000]                income(j) = Range("D" & i).Value　　　　' <--- Error Here, but why?[/COLOR]
                income_m(j) = Month(Range("D" & i).Offset(0, -2).Value)
                income_d(j) = Day(Range("D" & i).Offset(0, -2).Value)
                i = i + 1
                j = j + 1
                
        Loop

End If

Thanks!

 

[Takae]

You need Redim or Redim Preserve.
And, I think it will be eternity loop.

Do While i <> Rows.Count + 1

i= number of last row of colimnD.
Rows.Count + 1 =1048576 +1...?
The " i " will never same as "Rows.Count + 1".

 

[Crystalyzer]

First,

Do you mean to find the last row in column D or do you want the count of the number of income items?

If Application.WorksheetFunction.CountA(Range("D:D")) > 1 Then

With continuous data in column D you will get the LAST row in column D that contains a value

i = Range("D1").End(xlDown).Row

Rows.Count is giving you the total number of rows in the worksheet (1,048,576). Is that what you want?

Do While i <> Rows.Count + 1

All that being said, the error you are getting is because you did not size the array.

The code below will fill the arrays you are looking to fill

Sub Test2()    Dim income As Variant
    Dim income_m As Variant
    Dim income_d As Variant
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
    
    i = Cells(Rows.Count, "B").End(xlUp).Row
    
    ReDim income(i - 2)
    ReDim income_m(i - 2)
    ReDim income_d(i - 2)
    
    For j = 0 To i - 2
        income(j) = Cells(j + 2, 4).Value
        income_m(j) = Month(Cells(j + 2, 2).Value)
        income_d(j) = Day(Cells(j + 2, 2).Value)
    Next
End Sub

 

[WarPigl3t]

If Application.WorksheetFunction.CountA(Range("D:D")) > 1 Then

i = Range("D1").End(xlDown).Row
j = 0
Dim income() As String
Dim income_m() As String
Dim income_d() As String
Do Until j + 1 > i
ReDim Preserve income(j)
ReDim Preserve income_m(j)
ReDim Preserve income_d(j)
income(j) = Range("D" & j + 1).Value　　　　' <--- Error Here, but why?
income_m(j) = Month(Range("D" & j + 1).Offset(0, -2).Value)
income_d(j) = Day(Range("D" & j + 1).Offset(0, -2).Value)
j = j + 1

Loop

End If

 

[smallxyz]

Thank you all. I manage to figure out where my problem lies.

It works out well perfectly now. Thank you!

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

My new code now:


If Application.WorksheetFunction.CountA(Range("D:D")) > 1 Then

i = Range("D1").End(xlDown).Row
j = Range("D" & Rows.Count).End(xlUp).Row
n = 1

ReDim Preserve income(j - i + 1)
ReDim Preserve income_m(j - i + 1)
ReDim Preserve income_d(j - i + 1)

Do While i <> j + 1
income = Range("D" & i).Value
income_m = Month(Range("B" & i).Value)
income_d = Day(Range("B" & i).Value)
i = i + 1
n = n + 1