Finding a value in an array and returning a value in that row / column

advertisements

I am very basic at vba so unfortunately don't have a lot of ideas of how to do this type of search and related call.

I have an array like the below with each cell in the array a number from 0 to 100.

I want to create a table where for every value in the data set above 80, I return the heading from both the column and row of the cell. So at the first iteration the table would look something like this:

Table example

a 80 b

c 85 f

e 92 g

Array

abcdefghij

a

b

c

d

e

f

g

h

I

j

I think I have been specific but I don't really have a clue where to start. Thanks in advance for your help.


Something like this will get you started:

Sub CaptureCellsAboveValue(val as double, srcRange as Range, destRange as Range)
    Dim srcRrow as long, srcCol as Long, destRow as Long: destRow = 1
    For srcRow = 2 to srcRange.Rows.Count
        For srcCol = 2 to srcRange.Columns.Count
            If srcRange.Cells(srcRow, srcCol).Value > val Then
                destRange.Cells(destRow, 1).Value = _
                    srcRange.Cells(srcRow, 1).Value & " " & _
                    srcRange.Cells(srcRow, srcCol).Value  & " " & _
                    srcRange.Cells(1, srcCol).Value
                destRow = destRow + 1
            End If
        Next
    Next
End Sub

You can use it with the parameters:

  • Value: 80 in your example

  • srcRange: the table range including header row and header columns

  • destRange: the Top cell of the range where you want to out those results.