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:
a 80 b
c 85 f
e 92 g
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.