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 and here are some pointers how to avoid using Select and activate. — arcadeprecinct
– arcadeprecinct, Commented Oct 13, 2016 at 16:36

jeff carey · Accepted Answer · 2016-10-13 16:25:19Z

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, 2016 at 16:25

jeff carey

2,3833 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vivi11130704 Over a year ago

Yes I should have (i,0) instead of (i,1). Thanks!

kolcinx · Accepted Answer · 2016-10-13 16:36:45Z

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

Collectives™ on Stack Overflow

VBA Array doesn't work?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related