Help with Arrays

[Nerijus1]

Hi. I have just started VBA programming and now I am stuck on this division matter. I want to construct an array of values generated by (A2-A1)/A1, (A3-A2)/A2 ... etc for whole values in A. I could make Macros to write out the values on the other column, but its too time consuming. There are around 3000 values in my column.
Here is what I managed to come up with:


Dim rarray() As Variant
n = Application.CountA(Range("E:E"))
ReDim rarray(1 To n)
Range("E3").Select
For i = 1 To n
rarray(i) = (ActiveCell.Value - ActiveCell.Offset(-1, 0).Value) / ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(1, 0).Select
Next i


The problem is that it gets me to an overflow error. I know its because of the / sign, if I put *, + or - it works fine. Can anyone suggest a solution or tell me whats wrong? and guide me to some info in the help files about operations on the elements of the array.

 

[evnoort]

tried your code and n never gets a value so try determining n an other way

 

[Nerijus1]

For me n is fine, I get the correct value. Its the division sign (/) that keeps giving an error to me.

 

[NdNoviceHlp]

Option Base 1 maybe? HTH. Dave
ps. Stop using select! Refer to the data by range or cell designation.

 

[Jerry Sullivan]

Welcome to the Board Nerijus1,

Most likely your overflow error is due to dividing by zero. Do you have any zero or blank values in your range from E2 down?
If so, you need to think about how you want to handle those.

Here is some revised code which tests for zero values in the denominator.
If found, it puts a 0 value in rarray- you can edit that if you want.

The code also shows how you can reference a range directly instead of selecting it as NdNoviceHlp eloquently counseled.

Sub No_Overflow()
    Dim n As Long, i As Long
    Dim rarray() As Variant
    Dim cTop As Range
    Set cTop = Range("E3")
    n = Range("E" & Rows.Count).End(xlUp).Row - cTop.Row + 1
    ReDim rarray(1 To n)
    For i = 1 To n
        If cTop(i - 1) <> 0 Then
            rarray(i) = (cTop(i) - cTop(i - 1)) / cTop(i - 1)
        Else
            rarray(i) = 0 'or other value in place of #DIV/0!
        End If
    Next i
End Sub

Good luck!

 

[mikerickson]

The DIV/0 errors are because the range being calculated starts in A3, but has as many rows as there are entries in all of column A. There are two blank cells at the end of the range being looped through that cause a DIV/0

A loop is not needed.

Dim resultArray As Variant
Dim ExcelFormula As String

Rem calculate array
With Range("A:A")
    With Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp))
    
        ExcelFormula = "(" & .Address(, , , True) & "-" & .Offset(-1, 0).Address(, , , True) & ")/" & .Offset(-1, 0).Address(, , , True)
    
    End With
End With

resultArray = Evaluate(ExcelFormula)

Rem convert to 1-D array
resultArray = Application.Transpose(resultArray)

 

[Nerijus1]

Yes, thanks everyone for help. I just realized that myself after mikerickson post. Those couple empty cells were the problem.

 

[Jerry Sullivan]

A loop is not needed.

resultArray = Evaluate(ExcelFormula)

Rem convert to 1-D array
resultArray = Application.Transpose(resultArray)

Good to know....Thank you Mike.