Absolute Reference to the Left Until Change in Row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RyGuy - 26 Sep 2007 15:10 GMT

I made a post to the functions DG earlier in the week, thinking that the
issue was related to a function, but I did not get a solution there, and I am
not really sure it is a function-related question…I guess it is more of a
programming-related question.

Basically, I am trying to make a reference switch between relative and
absolute, when a value in Column A, sorted in ascending order, changes.

The values in Column A are evaluated in the following way:
If ActiveCell.Offset(-1, 0) <> ActiveCell.Offset(-1, -1)

The function in C2 is:
=IF(ROWS(C$2:C2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C2)),"")

The function in C3 is:
=IF(ROWS(C$2:C3)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C3)),"")

A close inspection reveals that =IF(ROWS(C$2:C2) changes to
=IF(ROWS(C$2:C3), and continues down in this fashion as long as the value in
Column A does not change. The C$2 has to remain absolute, but change to
something like Offset(-1, 0), when the value in Column A changes. For
instance, when the value in Column A does change, in my example it is row 10,
I need the function to change to:
=IF(ROWS(C$10:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A10,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$10:C10)),"")

Then, the value in Column A changes again on Row 11, so the next function
would have to be something like:
=IF(ROWS(C$11:C11)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A11,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C11)),"")

I created a Do Loop, and some logic, along with a piece of a recorded macro,
and came up with this (below):

Do Until ActiveCell.Offset(0, -1) = Empty
If ActiveCell.Offset(-1, 0) = ActiveCell.Offset(-1, -1) Then 'And ActiveCell
<> Empty And ActiveCell.Offset(1) <> Empty Then
Selection.FormulaArray = _

"=IF(ROWS(R2C:RC)<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import
Sheet'!R1C[-2]:R65000C[-2])),ROW('Import
Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C:RC)),"""")"
ActiveCell.Offset(1, 0).Select
End If

If ActiveCell.Offset(-1, 0) <> ActiveCell.Offset(-1, -1) Then
Selection.FormulaArray = _

"=IF(ROWS(R10C:RC)<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import
Sheet'!R1C[-2]:R65000C[-2])),ROW('Import
Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C:RC)),"""")"

ActiveCell.Offset(1, 0).Select
End If

Loop

I can't think of a way to get the Offset(0, -1) to be absolute when the
value in Column A changes and then change once again when the values in
Column A change again. I did some research on this DG and I believe the
problem is that I need to refer to a cell, such as this:
KeyCell = ActiveCell.Address(False, True)

I guess this makes the first part of:
ROWS(C$10:C10)
Rows absolute and the second pars to Rows relative. This is great, now I
need to add this to part of my code.

I’ve been using Excel, pretty heavy-duty, for over four years now and I have
never encountered something such as this before. Biff gave me a great
function, which does exactly what I want, but only when those ROWS(C$2:C2),
ROWS(C$10:C10), etc. change when the values in Column A change. Has anyone
dealt with this before? I can't help but think that I am making it wayyyy
more difficult that it should be. If anyone has any ideas, please share.

Thanks a bunch!
Ryan--

Reply to this Message

Bob Phillips - 26 Sep 2007 17:17 GMT

Is tis what you want?

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 Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
.Cells(iStart, "C").Resize(i - iStart).FormulaArray = _
Replace(sFormula, "<row>", iStart)
iStart = i
End If
Next i
End With

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I made a post to the functions DG earlier in the week, thinking that the
> issue was related to a function, but I did not get a solution there, and I
[quoted text clipped - 83 lines]
> Thanks a bunch!
> Ryan--

Reply to this Message

ryguy7272 - 26 Sep 2007 17:50 GMT

My goodness!! This is sooooo close. The only problem now is that it creates
an array, on my worksheet it goes from A2:A9. Then the values in Column A
change, and the next array goes from A10:A10, then the next array goes from
A11:A14. I guess there needs to be a slight change in the code so the macro
starts in C2, tests the value in Column A (same row) and then finds no change
so it goes to the next cell down, to C3, tests the value in Column A (same
row) and then finds no change so it goes to the next cell down, to C4, tests
the value in Column A (same row), etc. As it is now, the code builds an
array from A2:A9. I think it has to start in Column C, test the value in
Column A, then move down in Column C, offset(1, 0), then test the value in
Column A, then move down in Column C, offset(1, 0), etc.

Thanks a ton Bob!!

Signature

RyGuy

> Is tis what you want?
>
[quoted text clipped - 109 lines]
> > Thanks a bunch!
> > Ryan--

Reply to this Message

Bob Phillips - 26 Sep 2007 18:33 GMT

So are you saying you want a formula in A9 only, then A10, then A11, then
say A14, not in the other cells? If so, then

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 Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
.Cells(i-1, "C").FormulaArray = Replace(sFormula, "<row>",
iStart)
iStart = i
End If
Next i
End With

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> My goodness!! This is sooooo close. The only problem now is that it
> creates
[quoted text clipped - 140 lines]
>> > Thanks a bunch!
>> > Ryan--

Reply to this Message

ryguy7272 - 26 Sep 2007 20:02 GMT

Thanks again Bob, but it’s still not working :(

I’m 100% confident that this is doable; I’m just not explaining the issue
correctly.

In my example, the same values are in A2:A9 (it will always be different in
the future though). I count these values in Column B, and thus each cell in
range B2:B9 contains an 8. Then, in Column C, I am trying to get that mega
formula working (it returns cell addresses on another sheet). I’d like to
have the value in C2 show the cell address for the value in A2 (it happens to
be A17798) in the other sheet that corresponds to that value. Then in C3,
I’d like to see the cell address (this one is A17830) in the other sheet that
corresponds to the value in A3.

I know that the function in C2 has to be:
=IF(ROWS(C$2:C2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C2)),"")

The function in C3 has to be:
=IF(ROWS(C$2:C3)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C3)),"")

The function in C4 has to be:
=IF(ROWS(C$2:C4)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A4,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C4)),"")

Etc., down to C9. This works fine. Then, the values change in Column A.
If I continue with the routine described above, the function will be:
=IF(ROWS(C$2:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A10,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C10)),"")

This won’t work (for a reason unknown to me).

C10 has to be:
=IF(ROWS(C$10:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A10,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$10:C10)),"")

The, the value in Column A changes once again, and thus the function in A11
must be:
=IF(ROWS(C$11:C11)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A11,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C11)),"")

A12 is the same as A11, so the function in C12 must be:
=IF(ROWS(C$11:C12)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A12,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C12)),"")

I’m not exactly sure why Excel does this… I’ve been using this app. For a
long time and I’ve never seen anything quite like this. Also, I can figure
out, probably, 50%+, of all VBA code. Yours is pretty advanced; I can’t
follow the logic.

I’m going to mess with your code more, and see if I can get it working. If
you understand my dilemma, and if you can empathize with me, please post
back. I’ll really appreciate it if you can take one more crack at it.

Cordially,
Ryan---

Signature

RyGuy

> So are you saying you want a formula in A9 only, then A10, then A11, then
> say A14, not in the other cells? If so, then
[quoted text clipped - 167 lines]
> >> > Thanks a bunch!
> >> > Ryan--

Reply to this Message

ryguy7272 - 27 Sep 2007 14:52 GMT

Bob, 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

I'm pretty certain that this is what I need (if I could run it I would know
for sure). It is supposed to do a comparison b/w two values in Column B
(originally I said Column A, but the values in Column B are just a count of
the values in Column A, so B changes when A changes). 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.

This is probably simple for you. As for me, I've hit a wall; I am not sure
what to do now. Do you have any ideas about this?

Cordially,
Ryan--

Signature

RyGuy

> Thanks again Bob, but it’s still not working :(
>
[quoted text clipped - 225 lines]
> > >> > Thanks a bunch!
> > >> > Ryan--

Reply to this Message