How do I get the INDEX of a row in Excel VBA?

Can I use INDEX match in VBA?

Is there any way to use INDEX MATCH with VBA/VSTO? This is what I've been working on (it works as expected when I manually copy and paste the formula): Worksheets("Sheet1").Range("A3:C10").Range("A3") & "," & Worksheets("Sheet1").Range("B3") & ")"
I've tried several things but nothing is working. For example, this is how I thought of implementing it, but it doesn't work and it's also bad style, so please don't suggest that: Worksheets("Sheet1").Range("A3:C10").Range("A3:A10") & ")," & Worksheets("Sheet1").Range("B3") & ")"
Also I've tried with INDIRECT like this: Worksheets("Sheet1").Range("A3:C10").Range("A3") Offset(0, 0).Resize(Worksheets("Sheet1").Range("A3") End(xlDown).Row - 1).Address &
")," &. Worksheets("Sheet1").Range("B3") Offset(0, 0).Resize(Worksheets("Sheet1").Range("B3") End(xlDown).Row - 1).Address)
Both didn't work, I'm stuck, any ideas? What about changing your formula to. Worksheets("Sheet1").Range("A3:C10").Range("A3") & """","""" & Worksheets("Sheet1").

How do I get the cell reference of an active cell in VBA?

I have the following code, that selects the values I need in columns A and B depending on what is active: Dim cellValue1 As Long. Dim cellValue2 As Long. For Each myCell In Range("b2:b16").Cells cellValue1 = Cells(myCell.Row, "B").Value

If ActiveCell.Value = "yes" Then cellValue2 = Application.Match(cellValue1, Range("A"), 0) End If. Next. This works fine if the active cell is in column A, but if the active cell is in column B it will result in an error. I have searched and tried a few things, but haven't found any info.

I can work around this by: ActiveSheet.Range("A2").Value = cellValue2
Or
ActiveCell.Value = cellValue2 but since I have to find the value of column A, not the active cell (there might be multiple cells that are all in column A) this is not a very clean solution. I know I could use ActiveCell.Row but for some reason that doesn't work for me.

Is there any way to find the "cell reference" of an active cell or at least something that will indicate that the cell that is being referenced is in column A? If there is nothing like this in VBA then I am willing to create a method that will find the cell reference. I just want to make sure it isn't already out there before I do.

Thanks! You can get the row where your active cell resides by calling .Row from the ActiveCell property, however I don't see any reason to do so as the Range object itself contains the ActiveCell property directly: Dim cellValue1 As Long. Dim cellValue2 As Long. For Each myCell In Range("b2:b16").Cells cellValue1 = myCell.Value = "yes" Then cellValue2 = myCell.

How do I get the INDEX of a row in Excel VBA?

I'm writing a function that returns the INDEX of a cell in the first row of a particular column.)) Is there any way to do this in VBA? I'm not sure how to approach this. I was able to use WorksheetFunction.Match: Public Function MatchCell(Criteria As Range, TargetRange As Range) As Long. Dim rng1 As Range, rng2 As Range. Set rng1 = TargetRange.Resize(TargetRange.Rows.Count - 1)
Set rng2 = Criteria.Resize(Criteria.Count - 1)

'if there is a match, return the index of the target range. If WorksheetFunction.Match(rng1(1, 1), rng2, 0) Then MatchCell = rng1.Range("A" & lngRow).