Index/Match in VBA with criterion applied

[Karykatura]

Hi everyone,

First time posting so apologies if I commit any faux pas.
I have a classic problem with vlookup whereas I have multiple matching values but want to get a specific index.
I recently got into VBA and wrote a function which does the following:

• In sheet 1 Looks if cell value is equal to the one above it
• If it is, looks if condition is met (X = False)
• Index/Match values from sheet 1 to sheet 2 and inserts the result into a specified cell in sheet
• Moves onto next cell in a row

The only problem is my function doesn't work. The code is published below, any help/guidance would be very appreciated.

Sub OverdoingIt()
Dim iRow As Long
On Error Resume Next
Set rng = Sheets("1").Range("A2:A600")
For Each Cell In rng
If Sheets("1").Cell.Value = Sheets("1").Cell.Value - 1 Then
If Sheets("1").Cells.Column(25).Value = "False" Then
Sheets("2").Cells(iRow, 8).Value = Application.WorksheetFunction.Index(Sheets("1").Range("AA:AA"), Application.WorksheetFunction.Match(1, Sheets("2").Cell.Range("A:A"), 0), 0)
End If
End If
Next Cell
End Sub

 

[Fazza]

welcome

there are likely many possible approaches, some version dependent. and depending on the specifics of the data too (things like if there are only text, or only numbers, or mixed, blanks, errors, etc)

for anyone to give good assistance, please provide sample data & further information. including Excel version and whatever background info might be relevant

regards

 

[DanteAmor]

Hi everyone,

First time posting so apologies if I commit any faux pas.
I have a classic problem with vlookup whereas I have multiple matching values but want to get a specific index.
I recently got into VBA and wrote a function which does the following:

• In sheet 1 Looks if cell value is equal to the one above it
• If it is, looks if condition is met (X = False)
• Index/Match values from sheet 1 to sheet 2 and inserts the result into a specified cell in sheet
• Moves onto next cell in a row

The only problem is my function doesn't work. The code is published below, any help/guidance would be very appreciated.

Sub OverdoingIt()
Dim iRow As Long
On Error Resume Next
Set rng = Sheets("1").Range("A2:A600")
For Each Cell In rng
If Sheets("1").Cell.Value = Sheets("1").Cell.Value - 1 Then
If Sheets("1").Cells.Column(25).Value = "False" Then
Sheets("2").Cells(iRow, 8).Value = Application.WorksheetFunction.Index(Sheets("1").Range("AA:AA"), Application.WorksheetFunction.Match(1, Sheets("2").Cell.Range("A:A"), 0), 0)
End If
End If
Next Cell
End Sub

Your code has several problems.


First, you should not use the On Error Resume Next statement, because if you have an error in the code you will not know where the macro stops.

------------

• In sheet 1 Looks if cell value is equal to the one above it

This

If Sheets("1").Cell.Value = Sheets("1").Cell.Value - 1 Then

should be

If Cell.Value = Cell.Offset(-1).Value Then

---------------

• If it is, looks if condition is met (X = False)

This

[COLOR=#333333]If Sheets("1").Cells.[/COLOR][COLOR=#ff0000]Column(25)[/COLOR][COLOR=#333333].Value = "False" Then[/COLOR]

Should be

If Cells(Cells.Row, "X").Value = "False" Then

Or

If Cells(Cells.Row, "X").Value = False Then

--------------

iRow, it has no value, it is going to send you an error, you must explain in which line you want the result.

Sheets("2").Cells([COLOR=#ff0000]iRow[/COLOR], 8).Value

-------------
And finally you must explain with examples what you want to look for, where and what you expect of result.

Application.WorksheetFunction.Index(Sheets("1").Range("AA:AA"), Application.WorksheetFunction.Match(1, Sheets("2").Cell.Range("A:A"), 0), 0)

-----------
Then, forget the macro a bit, explain with examples what you need and what results you want.



---------
Some images of your sheets would help to understand your explanations.

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html