Loop array and change value

Question

With the below code I'm looping an array and if condition met to change array value. I'm receiving an error 424 on the line of changing value. Any ideas?

sub test()

    Dim arrAccSof As Variant

    arrAccSof = .Range(.Cells(3, 1), .Cells(MaxRowAccSof, 4))

    For j = LBound(arrAccSof) To UBound(arrAccSof)

        If IsEmpty(arrAccSof(j, 3)) Then
            arrAccSof(j, 3).Value = 0
        End If
        If IsEmpty(arrAccSof(j, 4)) Then
            arrAccSof(j, 4).Value = 0
        End If

    Next j

end sub

Pᴇʜ · Accepted Answer · 2019-05-13 12:13:52Z

3

An array item does not have a .Value it must be arrAccSof(j, 3) = 0. If you read values into an array like

arrAccSof = .Range(.Cells(3, 1), .Cells(MaxRowAccSof, 4))

it is the same as using

arrAccSof = .Range(.Cells(3, 1), .Cells(MaxRowAccSof, 4)).Value

and the array does only represent the values of the range but not a range object.

Note that if you change the values in the array they do not get changed in the cells automatically, unless you write the array values back to the cells in the end:

.Range(.Cells(3, 1), .Cells(MaxRowAccSof, 4)).Value = arrAccSof

In comparison if you do

Dim AccSof As Range
Set AccSof = .Range(.Cells(3, 1), .Cells(MaxRowAccSof, 4))

then AccSof is a referenece to the actual range object that can be used like the range itself:

AccSof(j, 3).Value = 0

This changes the cell value immediately.

But note that the first approach using the array is faster. I added this just to explain the difference.

edited May 13, 2019 at 12:13

answered May 13, 2019 at 12:05

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

1 Comment

T.M. Over a year ago

Concise & helpful :-)

Collectives™ on Stack Overflow

Loop array and change value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related