Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul

[RyGuy]

I almost never do multi-posts, and never cross-post, but I just realized that
I actually posed my VBA question in the Excel-Functions area (I never do that
either). I think many Excel-VBA experts here visit the Excel-Functions area
on a pretty frequent basis, but I’m trying to move things along a little by
posting in the place where I actually intended to post this morning. Below
is the exact question I posted in the Functions area (if I get my questions
answered here I’ll post the final results there for closure).

As I stated in the other area…I would be so thrilled to finish this project
before COB today. I’ve 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, I’d 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, I’d 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<row>C: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<row>C: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.

I’ve 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 I’m about 95% of the way
there; I just need a push to get that last 5%.


Cordially,
Ryan--

 

[JLGWhiz]

Maybe I missed it, but I don't see where cell is defined for use in this line:

fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1),

If VBA don't know what cell is, then it regurgitates. Maybe i.Row?

Another thing I notice is your use of ActiveCell, but I cannot discern
exactly where the active cell is at that juncture. If you are evaluating
column B against column A then you might want to use something like:

If Cells(i, 1) <> Cells(i-1, 1) Then

Which would evaluate two vertically adjacent cells in column A. The point
being that i will control the row number so you will now where the focus is.

Of course, these are my preferences and you might have others.

I almost never do multi-posts, and never cross-post, but I just realized that
I actually posed my VBA question in the Excel-Functions area (I never do that
either). I think many Excel-VBA experts here visit the Excel-Functions area
on a pretty frequent basis, but I’m trying to move things along a little by
posting in the place where I actually intended to post this morning. Below
is the exact question I posted in the Functions area (if I get my questions
answered here I’ll post the final results there for closure).

As I stated in the other area…I would be so thrilled to finish this project
before COB today. I’ve 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, I’d 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, I’d 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<row>C: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<row>C: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.

I’ve 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 I’m about 95% of the way
there; I just need a push to get that last 5%.


Cordially,
Ryan--

 

[Tom Ogilvy]

Sub Final2()

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<row>C: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<row>C:RC)),"""")"

for i = 3 to ilastrow + 1
if .cells(i,"B") <> cells(i-1,"B") then
.cells(i,"C").Filldown
else
.cells(i,"C").FormulaArray = Replace(sFormula,"<row>",i - 1)
end if
Next
End with

--
Regards,
Tom Ogilvy

I almost never do multi-posts, and never cross-post, but I just realized that
I actually posed my VBA question in the Excel-Functions area (I never do that
either). I think many Excel-VBA experts here visit the Excel-Functions area
on a pretty frequent basis, but I’m trying to move things along a little by
posting in the place where I actually intended to post this morning. Below
is the exact question I posted in the Functions area (if I get my questions
answered here I’ll post the final results there for closure).

As I stated in the other area…I would be so thrilled to finish this project
before COB today. I’ve 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, I’d 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, I’d 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<row>C: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<row>C: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.

I’ve 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 I’m about 95% of the way
there; I just need a push to get that last 5%.


Cordially,
Ryan--

 

[RyGuy]

Yes, JLGWhiz! There seems to be some kind of problem with this:
fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1)

I thought it would start at C2, and based on the evaluation routine,
(I am now using If Cells(i, 1) <> Cells(i-1, 1) Then...If Cells(i, 1) =
Cells(i-1, 1) Then...), either fill down one cell, autofill function from the
cell above, or enter a new function (which is defined a "sFormula"). I am
missing, but I don't know what.

Thanks,
Ryan--

Maybe I missed it, but I don't see where cell is defined for use in this line:

fillrange.AutoFill Destination:=Range("C2:C" & cell.Row + 1),

If VBA don't know what cell is, then it regurgitates. Maybe i.Row?

Another thing I notice is your use of ActiveCell, but I cannot discern
exactly where the active cell is at that juncture. If you are evaluating
column B against column A then you might want to use something like:

If Cells(i, 1) <> Cells(i-1, 1) Then

Which would evaluate two vertically adjacent cells in column A. The point
being that i will control the row number so you will now where the focus is.

Of course, these are my preferences and you might have others.

I almost never do multi-posts, and never cross-post, but I just realized that
I actually posed my VBA question in the Excel-Functions area (I never do that
either). I think many Excel-VBA experts here visit the Excel-Functions area
on a pretty frequent basis, but I’m trying to move things along a little by
posting in the place where I actually intended to post this morning. Below
is the exact question I posted in the Functions area (if I get my questions
answered here I’ll post the final results there for closure).

As I stated in the other area…I would be so thrilled to finish this project
before COB today. I’ve 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, I’d 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, I’d 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<row>C: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<row>C: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.

I’ve 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 I’m about 95% of the way
there; I just need a push to get that last 5%.


Cordially,
Ryan--

 

[RyGuy]

Tom, thank you SOOOOOOOOOO much! This was baffling me for the greater part
of two weeks. I knew it was possible, but I couldn't see the solution; I
just didn't have the experience or the insight that many others on this DG do
have. Your code got me (probably) 99.99% of the way there. I just modified
it a little, to get the exact results that I wanted. My final solution is
shown below:

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<row>C: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<row>C:RC)),"""")"

iStart = 2
For i = 2 To iLastRow
If Cells(i, 2) = Cells(i - 1, 2) Then
..Cells(i, "C").FormulaArray = Replace(sFormula, "<row>", i - 1)
..Cells(i, "C").FillDown
Else
..Cells(i, "C").FormulaArray = Replace(sFormula, "<row>", i)
End If
Next
End With

End Sub


Thanks to all who helped along the way!!
Thank you 1,000,000*!
Ryan--

Sub Final2()

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<row>C: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<row>C:RC)),"""")"

for i = 3 to ilastrow + 1
if .cells(i,"B") <> cells(i-1,"B") then
.cells(i,"C").Filldown
else
.cells(i,"C").FormulaArray = Replace(sFormula,"<row>",i - 1)
end if
Next
End with

--
Regards,
Tom Ogilvy

I almost never do multi-posts, and never cross-post, but I just realized that
I actually posed my VBA question in the Excel-Functions area (I never do that
either). I think many Excel-VBA experts here visit the Excel-Functions area
on a pretty frequent basis, but I’m trying to move things along a little by
posting in the place where I actually intended to post this morning. Below
is the exact question I posted in the Functions area (if I get my questions
answered here I’ll post the final results there for closure).

As I stated in the other area…I would be so thrilled to finish this project
before COB today. I’ve 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, I’d 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, I’d 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<row>C: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<row>C: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.

I’ve 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 I’m about 95% of the way
there; I just need a push to get that last 5%.


Cordially,
Ryan--