Check value of several cells in one row, trying to shorten If Then statement

[EricL89]

I'm trying to have my macro check if cells in a row on two different sheets match. I'm able to get it to work using And & Offset, but I'd like to know if there's another method to accomplish the same thing in a shorter code. This is essentially what I've got so far:

Dim Sheet1 as Worksheet
Dim Sheet2 as Worksheet
Dim iCells as Range
Dim iRows as Range
Dim iLastRow As Integer
Dim iMatch as Boolean

Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
Set Sheet2 = ThisWorkbook.Sheets("Sheet2")

iLastRow = Sheet2.Range("A2").End(xlDown).Row
Set iRows = Sheet2.Range("A2:A" & iLastRow)
iMatch = False

For Each iCells In iRows
If iCells.Value = ActiveCell.Value Then

If iCells.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value And iCells.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value And iCells.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value And iCells.Offset(0, 4).Value = ActiveCell.Offset(0, 4).Value Then

****************

This last line is the one I'm trying to shorten. In reality, it goes on until .Offset(0,7) and the two sheets are also in separate workbooks. Aside from that, the basis is the same and I'm just trying to find a way to shorten the code.

I've tried doing this by using the value of the range (i.e. Range(iCells, iCells.Offset(0,7)).Value ), but I couldn't get it to work as I thought it would.

Does anybody have any thoughts? I'm getting a little tired of repeatedly using "Offset And Offset And Offset And Offset". Thanks!

 

[Stoner7Matt]

Do you have to do this in VBA? Honestly I would tell VBA to create a relative INDEX MATCH function. However i'm curious. Is there an advantage to doing this in VBA or does this just simplify the task for the end-user?

 

[EricL89]

Do you have to do this in VBA? Honestly I would tell VBA to create a relative INDEX MATCH function. However i'm curious. Is there an advantage to doing this in VBA or does this just simplify the task for the end-user?

Typically, I wouldn't go through the extra effort of doing this in VBA. However, this is one small part of a larger macro, and that "OFFSET AND" sequence appears about 6 times throughout the whole code. The macro is being used on multiple workbooks to organize prospects between 3 worksheets (New, Sold, & Lost), after which it then updates to a "Master" workbook. In the end, the purpose of the macro is to simplify the task for my co-workers who are not too proficient with Excel.

While this current "OFFSET AND" sequence works fine, I'm also curious to know if there is a shorter/easier way to accomplish the same task. How would you go about doing the INDEX MATCH in the VBA? My original thoughts were trying to use EXACT as a WORKSHEETFUNCTION, but that particular function wasn't an option.

 

[EricL89]

The only other method I used so far which accomplishes the same thing, would be to add in another "FOR EACH" sequence. While it's not a run-on like the "OFFSET AND" repetition I'm currently using, it's also not as simple so I'm more inclined to leave it with the "OFFSET AND" repetition.

I'm really looking for a short & simple way to say something like:

IF Range(iCells.Offset(0,1),iCells.Offset(0,5)).Value = Range(ActiveCell.Offset(0,1), ActiveCell.Offset(0,5)).Value THEN

Something where it compares the values of the cells in the two ranges. Is something like this possible?