How do I reference a cell in Excel through a variable and access all its properties in Visual Basic?

Question

I am working on a Macro in Excel that needs to iterate through an entire column and find clusters of non-zero data, add up the data and store the result near the mentioned cluster. Then it continues down the column looking for other clusters and doing the same thing.

That being said, I am trying to store a reference to the "target" cell (where the addition of the cluster will be stored) in a variable, and then using that variable to access the "value" property of the cell so that I can make changes to it.

Here's the code:

 Sub addNonZeroes()

 Dim targetCell

 ' Select cell E5, *first line of data*.
 Range("E5").Select
 ' Set Do loop to stop when an empty cell is reached.
 Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value <> 0 Then
         targetCell = ActiveCell.Offset(1, 0)
            Do Until ActiveCell.Value = 0
                'ERROR OCCURS HERE
                targetCell.Value = ActiveCell.Value + targetCell.Value
                ActiveCell.Offset(0, 1).Select
            Loop
    End If

    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
 Loop

 End Sub

Answer 1

Source of the code error

The error occur because your targetCell is not really defined.

You should:

• define your variable and type it
• use the Set keyword to assign an object

Here is your code adapted:

Sub addNonZeroes()

 Dim targetCell As Range

 ' Select cell E5, *first line of data*.
 Range("E5").Select
 ' Set Do loop to stop when an empty cell is reached.
 Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value <> 0 Then
         Set targetCell = ActiveCell.Offset(1, 0)
            Do Until ActiveCell.Value = 0
                'ERROR OCCURS HERE
                targetCell.Value = ActiveCell.Value + targetCell.Value
                ActiveCell.Offset(0, 1).Select
            Loop
    End If

    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
 Loop

 End Sub

Using Select and Offset is a bad idea

Yet, you should consider not using Select and Offset because this is very slow.

A better way would be this kind of loop:

Dim c As Range
For Each c in Range("E5:E100")
   If c.Value <> 0 Then
      'do whatever
   End If
Next c

Using range to array is a very VBA way

Note that you can use a Variant to store every value of a range into an array, something like this:

Dim arr As Variant, i As Integer
arr = Range("E5:E100").Value
For i = LBound(arr , 2) To UBound(arr , 2)
  'check for any empty value
Next i

See this thread for more examples: Array from Range in Excel VBA