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

[drmingle]

0 1 2 3 4 5.0 12.0 13.00 14.0 2.00 1.0 4.00 #NA #NA #NA 15.0 3.0 9.0 7.0 17.00 8.0 6.0 11.0 10.0 16.00 <tbody> </tbody>
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
Thanks in advance for the help

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[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 ?