Match worksheet function in VBA searching on multiple criteria

[Mellstock]

Thank you Paul for your effort and your post. I think I have found a single line of code answer which I posted about earlier. It seems pretty robust so far. I am wondering why given all the things Excel and VBA do very well why it is so difficult to essentially return data from a search based on two columns.

[Paul_Hossler]

Thank you Paul for your effort and your post. I think I have found a single line of code answer which I posted about earlier. It seems pretty robust so far. I am wondering why given all the things Excel and VBA do very well why it is so difficult to essentially return data from a search based on two columns.

Sub Findclose()
Range("K4").Select
Range("K4").Value = WorksheetFunction.Index(Range("F2:F34"), WorksheetFunction.Match(Range("K2"), Range("A2:A34"), 0) + WorksheetFunction.Match(Range("K3"), Range("B2:B34"), 0) - 1)
End Sub

1. What do you plan to do when you have 35 rows of data?

2. Don't need to select the cell to use it

[Mellstock]

Hi Paul, I like to work with a small dataset before rolling out to a bigger one. When the time comes I would probably use something like or involving Cells(Rows.Count,1).End(xlUP).

Yes I know I select Ranges and cells more often than I need to. I helps me demonstrate to myself that I am manipulating the spreadsheet the way I am wanting to.

I also sometimes need to leave a cell selected in a particular place in order to run the next procedure to be called. Thank you for your interest,


Ian