0

I am working on an OR problem in Excel, and I am trying to list the cells in the matrix that contain values. For example, if I had the identity matrix, I would like the function to return values A1, B2, C3.

The reason being, I want to create variable cells for those that contain values that are true. I will then use these cells in solver.

Dwevon
  • 15
  • 1
  • 3

2 Answers2

0

Here a function that will return you those addresses for a range in a collection.

Public Function hasValue(matrix As Range) As Collection
    Set hasValue = New Collection
    Dim c As Range
    For Each c In matrix
        If c.Value Then
            hasValue.Add c
        End If
    Next c
End Function
Chabu
  • 121
  • 8
  • How exactly does a collection work. Does this return as a range of cells that I can concatenate? – Dwevon Mar 31 '16 at 23:13
0

Here's pretty much all you can do with a collection. you should try it out yourself. Enter a matrix of values in a sheet, select the range and call the test macro.

Public Sub test()
    Dim r As Collection
    Set r = hasValue(Selection)
    Debug.Print " number of elements: " & r.Count
    r.Remove (1)
    Debug.Print " number of elements: " & r.Count
    Debug.Print TypeName(r(1))
    Dim c As Range
    For Each c In r
        Debug.Print c.Address
        Debug.Print c.Value
    Next c
End Sub

It contains whatever obect you put in it, in this case it is ranges.

Chabu
  • 121
  • 8