| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Absolute Reference to the Left Until Change in Row
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(SEAR CH(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(SEAR CH(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. Ive 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-- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyGuy" wrote in message ... 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(SEAR CH(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(SEAR CH(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-- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
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!! -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyGuy" wrote in message ... 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(SEAR CH(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(SEAR CH(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-- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ryguy7272" wrote in message ... 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!! -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyGuy" wrote in message ... 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(SEAR CH(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(SEAR CH(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-- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
Thanks again Bob, but its still not working :(
Im 100% confident that this is doable; Im 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). Id 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, Id 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(SEARC H(A10,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C10)),"") This wont work (for a reason unknown to me). C10 has to be: =IF(ROWS(C$10:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEAR CH(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(SEAR CH(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(SEAR CH(A12,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C12)),"") Im not exactly sure why Excel does this€¦ Ive been using this app. For a long time and Ive never seen anything quite like this. Also, I can figure out, probably, 50%+, of all VBA code. Yours is pretty advanced; I cant follow the logic. Im 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. Ill really appreciate it if you can take one more crack at it. Cordially, Ryan--- -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ryguy7272" wrote in message ... 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!! -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyGuy" wrote in message ... 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(SEAR CH(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(SEAR CH(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-- |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
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<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 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-- -- RyGuy "ryguy7272" wrote: Thanks again Bob, but its still not working :( Im 100% confident that this is doable; Im 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). Id 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, Id 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(SEARC H(A10,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C10)),"") This wont work (for a reason unknown to me). C10 has to be: =IF(ROWS(C$10:C10)<=B$1,"A"&SMALL(IF(ISNUMBER(SEAR CH(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(SEAR CH(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(SEAR CH(A12,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$11:C12)),"") Im not exactly sure why Excel does this€¦ Ive been using this app. For a long time and Ive never seen anything quite like this. Also, I can figure out, probably, 50%+, of all VBA code. Yours is pretty advanced; I cant follow the logic. Im 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. Ill really appreciate it if you can take one more crack at it. Cordially, Ryan--- -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "ryguy7272" wrote in message ... 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!! -- RyGuy "Bob Phillips" wrote: 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<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 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyGuy" wrote in message ... 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(SEAR CH(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(SEAR CH(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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortcut to change change cell reference to Absolute reference? | Excel Worksheet Functions | |||
| Change linked cell reference to absolute | Excel Programming | |||
| Change a cell reference from relative to absolute | Excel Discussion (Misc queries) | |||
| change change cell reference to Absolute reference | Excel Discussion (Misc queries) | |||
| Absolute Reference to the Left Until Change in Row | Excel Worksheet Functions | |||
Linear Mode
