VBA Array doesn't work?

Question

I have this practice file with 5 order prices. The goal is to add $20 to each of the record and have a message box to display the result.

Here is the data:

My code is this:

Sub TotalDelivery()
Dim curDelCharge As Currency
Dim curTotal(4)

Dim i As Integer

Worksheets("Sheet1").Range("B10").Activate

Const curDelCharge = 20

For i = 0 To 4

curTotal(i) = ActiveCell.Offset(i, 1).Value + curDelCharge

MsgBox (curTotal(i))

Next i
End Sub

However the message box only displays 20 which is only my curDelCharge value.

To debug, I change the msgbox code into: MsgBox (ActiveCell.Offset(i, 1).Value)

The return value is blank which means the code doesn't read my ActiveCell value. Why is that?

Thanks in advance!

[Here](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/9292/avoid-using-select-or-activate) and [here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) are some pointers how to avoid using `Select` and `activate`. — arcadeprecinct, Oct 13 '16 at 16:36

score 3 · Accepted Answer · answered Oct 13 '16 at 16:25

3

This line:

curTotal(i) = ActiveCell.Offset(i, 1).Value + curDelCharge

should instead be:

curTotal(i) = ActiveCell.Offset(i, 0).Value + curDelCharge

Putting a "1" will move the offset 1 column to the right, which you don't want.

answered Oct 13 '16 at 16:25

jeff carey

2,313
3
13
17

Yes I should have (i,0) instead of (i,1). Thanks! – vivi11130704 Oct 13 '16 at 16:45

score 1 · Answer 2 · answered Oct 13 '16 at 16:36

Sub TotalDelivery()
Dim curTotal(4)
Dim i As Integer
Dim rngCellsToChange As Range 'range of cells you are targeting
Dim rCell As Range 'individual cell in collection of cells. See alternative solution below

'You can refer to cells directly, without activating them.
'You are highly discouraged to use Activate or Select methods.
'Use ThisWorkbook to explicitly tell VBA, which workbook you are targeting
Set rngCellsToChange = ThisWorkbook.Worksheets("Sheet1").Range("B10:B14")

Const curDelCharge = 20

For i = 0 To 4
    curTotal(i) = rngCellsToChange(i + 1).Value + curDelCharge
    MsgBox (curTotal(i))
Next i

'Alternatively, you can use the Range object to loop through all it's cells, like so:
For Each rCell In rngCellsToChange
    MsgBox rCell.Value + curDelCharge
Next

End Sub

VBA Array doesn't work?

2 Answers2