How to find cell value based on other cell value using VBA?

[drmingle]

[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5.0
[/TD]
[TD]12.0
[/TD]
[TD]13.00
[/TD]
[TD]14.0
[/TD]
[TD]2.00
[/TD]
[/TR]
[TR]
[TD]1.0
[/TD]
[TD]4.00
[/TD]
[TD]#NA
[/TD]
[TD]#NA
[/TD]
[TD]#NA
[/TD]
[/TR]
[TR]
[TD]15.0
[/TD]
[TD]3.0
[/TD]
[TD]9.0
[/TD]
[TD]7.0
[/TD]
[TD]17.00
[/TD]
[/TR]
[TR]
[TD]8.0
[/TD]
[TD]6.0
[/TD]
[TD]11.0
[/TD]
[TD]10.0
[/TD]
[TD]16.00
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The top row contain the cell information I am looking for based on rows 2-5.

My objective is to find the number 1.0 in all rows 2-5 and pull back the corresponding number in the column of row 1. (In theory since I am using a rank function, I am not worried about multiple 1's.)

For example, 1.0 is in column 1 and row 3, so I would need to have a VBA code snippet pull back the number 0 at the top of the column and use it as a variable for a filter.

Ultimately I am wanting to use the variable (in the case 0) to perform a filter function using the below code:

Sub Count_2()
'

'
    Sheets("_Study_Train").Select
    ActiveSheet.Range("$A$1:$P$707").AutoFilter Field:=15, Criteria1:="2"
    ActiveSheet.Range("$A$1:$P$707").AutoFilter Field:=16, Criteria1:="0"
    Cells.Select
    Selection.Copy
    Sheets("Tmp_Table").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("AlgoTweak").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "=COUNT(Tmp_Table!R[-4]C[13]:R[133]C[13])"
    Range("C6").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tmp_Table").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("_Study_Train").Select
    ActiveSheet.Range("$A$1:$P$707").AutoFilter Field:=16, Criteria1:="1"
    Cells.Select
    Selection.Copy
    Sheets("Tmp_Table").Select
    ActiveSheet.Paste
    Sheets("AlgoTweak").Select
    Range("D6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=COUNT(Tmp_Table!R[-4]C[12]:R[67]C[12])"
    Range("D6").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tmp_Table").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("_Study_Train").Select
    ActiveSheet.ShowAllData
    Sheets("AlgoTweak").Select
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "=R[-7]C/SUM(R6C3:R6C7)"
    Range("C13").Select
    Selection.AutoFill Destination:=Range("C13:D13"), Type:=xlFillDefault
    Range("C13:D13").Select
    Range("H13").Select
End Sub

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Thanks in advance for the help
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[patel45]

My objective is to find the number 1.0 in all rows 2-5 and pull back the corresponding number in the column of row 1.

can you show the desired table ?