I would be so thrilled to finish this project before COB today. Ive been
trying, for about two weeks, to find a solution to a problem of identifying
cell addresses and then comparing the cell to the left (offset(-1,0)) with
the cell to the left and up one (offset(-1,-1)). If these cells contain the
same values, Id like to move down one cell (in the current column) and do a
simple xlFillDefault. If the cell to the left, and to the left and up one,
are different, Id like to do a search for the value in column A, and return
the cell address that matches this value on another sheet.


This is where I am now:

Sub Final2()
Dim iStart As Long
Dim sFormula As String
Dim iLastRow As Long
Dim i As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
sFormula = "=IF(ROWS(R<rowC:RC)<=R1C[-1],""A""&" & _
"SMALL(IF(ISNUMBER(SEARCH(RC[-2]," & _
"'Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROW('Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROWS(R<rowC:RC)),"""")"

iStart = 2
For i = 3 To iLastRow + 1
If ActiveCell.Offset(-1, 0).Value < ActiveCell.Offset(-1, -1).Value Then
..Cells(i - 1, "C").FormulaArray = Replace(sFormula, "<row", iStart)
iStart = i
End If

If ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, -1).Value Then
Set fillrange = Range(ActiveCell, ActiveCell.Offset(1, 0))
fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1),
Type:=xlFillDefault
iStart = i
End If

Next i
End With
End Sub

The macro fails at this line:
fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1),
Type:=xlFillDefault


The second If never seems to get evaluated, and it should because the cells
on the left are not always equal. If I change the first If to:
If ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, -1).Value Then
€¦and the second IF to:
If ActiveCell.Offset(-1, 0).Value < ActiveCell.Offset(-1, -1).Value Then

€¦then the code fails at this line:
fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1),
Type:=xlFillDefault


Again, this is supposed to do a comparison b/w two values in Column B, then,
in the same row as the current row and the row above, the macro should take
the value in
Column C, and fill down one row if there is a match b/w the two values in
Column B. If there is no match, then it should loop back to the first part of
the For-Next loop, which enters the array-type function into the current cell
in Column C.

Ive received great help from Bob, Joel, OssieMac, Max, Peo, and especially
T. Valko (thank you so much). Now, I've hit a wall; I am not sure what to do
now. Does anyone have any ideas about this? I think Im about 95% of the
way there; I just need a push to get that last 5%.


Cordially,
Ryan--