Help with Arrays

N

Nerijus1

New Member
Joined
Aug 17, 2011
Messages
3
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
For me n is fine, I get the correct value. Its the division sign (/) that keeps giving an error to me.
 
Upvote 0
Option Base 1 maybe? HTH. Dave
ps. Stop using select! Refer to the data by range or cell designation.
 
Upvote 0
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.

Code: 
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!
 
Upvote 0
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.

Code: 
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)
 
Upvote 0
Yes, thanks everyone for help. I just realized that myself after mikerickson post. Those couple empty cells were the problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
157
campbell1093
C
Rhodie72
  • Question Question
How to write a data to a text file (VBA)
2
Replies
17
Views
1K
Rhodie72
Rhodie72
T
  • Question Question
Reset multiple dependent drop down boxes in a spread sheet.
Replies
2
Views
196
Tarnz
T
D
  • Question Question
why show the frame of userform as the photo1
Replies
0
Views
210
david2005wang
D
S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
369
ststern45
S

Forum statistics

Threads
1,224,618
Messages
6,179,919
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top