VBA code with Index & Match but uses a variable column and row number from a cell value

[benajamingeldart]

Hi,

I have managed to get the results i need to an Index & Match Search within a defined array but now want to search for a specified header and identify the column it sits in then identify a different criteria to search that returns the the row the data resides.

Then I want to use the column and row as a variable in the formula;
=INDEX(Conversion!$A$1:$ZZ$60000,MATCH(A2,Conversion!A:A,0),MATCH(E2,Conversion!$A$1:$ZZ$1,0))": Range("F2:F" & lastrow).FillDown

where A:A is replaced with a column number variable and $A$1:$ZZ1 is replaced with the row variable. This should give me a lot of flexibility to search and extract rather then know the specific ranges that would need to be chanegd each time.

The code i'm manipulating is below but not successfully;

Sub ElementQuantity()
'Inserts formula to search for element quantity from conversion sheet based on station ID and element symbol and drags down to last value in column A
Application.ScreenUpdating = False
Dim lastrow As Long
Dim StationIDCol As Long
Dim NickelIDRow As Long
Dim ws As Worksheet

StationIDCol = Sheets("Cruise Log").Range("G1")
NickelIDRow = Sheets("Cruise Log").Range("G2")
Set ws = ActiveWorkbook.Sheets("Results")

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Worksheets("Conversion").Select
Range("F2") = Application.WorksheetFunction.Index(ws.Range("$A$1:$zz$60000"), Application.WorksheetFunction.Match(Range("A2").Value, ws.Cells(StationIDCol), 0), application.WorksheetFunction.Match(Range("E2").Value, ws.Cells(NickelIDRow)), 0): Sheets("Conversion").Range("F2:F" & lastrow).FillDown
End Sub

Thanks.

 

[RayFrye]

Why not cram the formula directly into the cell

range("F2")  =  "INDEX(Conversion!$A$1:$ZZ$60000,MATCH(A2,Conversion!" & variable1  & " ,,0),MATCH(E2,Conversion!" & variable2 & ",0))": Range("F2:F" & lastrow).FillDown

 

[RayFrye]

Hmm, first time I've tried the VBA code insertion option, looks a little wild but you will get the general idea.

 

[Fluff]

If you want to use your own formatting in the code, then you need to use the <rich/> code tags, otherwise use the <vba/> tags.