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

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
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:

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>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,693
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top