| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute Reference to the Left Until Change in Row
I am aware of a few ways to reference a cell to the left of the active cell,
such as: =INDIRECT("RC[-1]",0) =OFFSET(B1,-1,0) I am curious to know if there is a way to link to this cell as an absolute reference, such as being in cell B1, and referencing $A$1, and then moving down to the next cell, by means of: ActiveCell.Offset(1, 0).Select €¦and continuing in this fashion while checking values in Column A: If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then €˜do stuff here =INDIRECT("RC[-1]",0) €˜do stuff here =OFFSET(B1,-1,0) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then €˜ stop the OFFSET function or INDIRECT function, and make new absolute reference to left of the currently active cell. I am having a difficult time getting the getting the absolute reference to stick in VBA, and then getting it to return to a relative reference when: Offset(0, -1) < Offset(-1, 1) Does anyone have any ideas about this? Thanks a bunch! Ryan-- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
Hi Ryan,
I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
The code looks like this:
Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop I was trying to get the part: R10C of ROWS(R10C[-1]:RC[-1]) to be absolute if there is a change in values in Column A (which is evaluated by the following) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) I can't think of a way to get the R10C to be absolute and then change once 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. Regards, Ryan--- -- RyGuy "OssieMac" wrote: Hi Ryan, I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
I made a little progress b/w last night and today. I believe the problem is
that I need to refer to a cell, such as this: KeyCell = ActiveCell.Address(False, True) I found the code on this DG. I guess this makes the Row absolute and the Column relative. This is great, now I need to add this to part of my code: In the second part of the loop, if the values in Column A evaluate to < prior cell, I would like to replace this: ROWS(R2C[-1]:RC[-1]) With this: KeyCell = ActiveCell.Address(False, True) Key Cell = ROWS(R2C[-1]:RC[-1) < -- somehow the rows have to be an absolute reference, until there is a change in Column A The entire loop is shown below: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop The ultimate objective is to get Excel to give me an absolute reference in Cell C2 (B2 is being referenced), such as (ROWS(B$2:B2), (ROWS(B$2:B3), (ROWS(B$2:B4), down to the change in value in Column A, which could be anywhere, but in my current scenario, it is in A10. When I get to A10, I would like Excel to change the reference to (ROWS(B$10:B10). Then there is a change at A11, so I would like Excel to reference (ROWS(B$11:B11), (ROWS(B$11:B12), etc€¦down to the next change. Does this make sense? Is it possible? Thanks, Ryan-- "ryguy7272" wrote: The code looks like this: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop I was trying to get the part: R10C of ROWS(R10C[-1]:RC[-1]) to be absolute if there is a change in values in Column A (which is evaluated by the following) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) I can't think of a way to get the R10C to be absolute and then change once 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. Regards, Ryan--- -- RyGuy "OssieMac" wrote: Hi Ryan, I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
Hi Ryan,
I'm sorry but I don't have the answers to this. Perhaps someone else can help. Regards, OssieMac "RyGuy" wrote: I made a little progress b/w last night and today. I believe the problem is that I need to refer to a cell, such as this: KeyCell = ActiveCell.Address(False, True) I found the code on this DG. I guess this makes the Row absolute and the Column relative. This is great, now I need to add this to part of my code: In the second part of the loop, if the values in Column A evaluate to < prior cell, I would like to replace this: ROWS(R2C[-1]:RC[-1]) With this: KeyCell = ActiveCell.Address(False, True) Key Cell = ROWS(R2C[-1]:RC[-1) < -- somehow the rows have to be an absolute reference, until there is a change in Column A The entire loop is shown below: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop The ultimate objective is to get Excel to give me an absolute reference in Cell C2 (B2 is being referenced), such as (ROWS(B$2:B2), (ROWS(B$2:B3), (ROWS(B$2:B4), down to the change in value in Column A, which could be anywhere, but in my current scenario, it is in A10. When I get to A10, I would like Excel to change the reference to (ROWS(B$10:B10). Then there is a change at A11, so I would like Excel to reference (ROWS(B$11:B11), (ROWS(B$11:B12), etc€¦down to the next change. Does this make sense? Is it possible? Thanks, Ryan-- "ryguy7272" wrote: The code looks like this: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop I was trying to get the part: R10C of ROWS(R10C[-1]:RC[-1]) to be absolute if there is a change in values in Column A (which is evaluated by the following) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) I can't think of a way to get the R10C to be absolute and then change once 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. Regards, Ryan--- -- RyGuy "OssieMac" wrote: Hi Ryan, I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
Thanks for the help OssieMac. I think this is a bit more related to
programming than it is related to functions. I think I will try a similar post in the programming DG. In the meantime, I will continue to work out a solution by myself. If I can get something workable, I will post back here for closure. Regards, Ryan-- "OssieMac" wrote: Hi Ryan, I'm sorry but I don't have the answers to this. Perhaps someone else can help. Regards, OssieMac "RyGuy" wrote: I made a little progress b/w last night and today. I believe the problem is that I need to refer to a cell, such as this: KeyCell = ActiveCell.Address(False, True) I found the code on this DG. I guess this makes the Row absolute and the Column relative. This is great, now I need to add this to part of my code: In the second part of the loop, if the values in Column A evaluate to < prior cell, I would like to replace this: ROWS(R2C[-1]:RC[-1]) With this: KeyCell = ActiveCell.Address(False, True) Key Cell = ROWS(R2C[-1]:RC[-1) < -- somehow the rows have to be an absolute reference, until there is a change in Column A The entire loop is shown below: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop The ultimate objective is to get Excel to give me an absolute reference in Cell C2 (B2 is being referenced), such as (ROWS(B$2:B2), (ROWS(B$2:B3), (ROWS(B$2:B4), down to the change in value in Column A, which could be anywhere, but in my current scenario, it is in A10. When I get to A10, I would like Excel to change the reference to (ROWS(B$10:B10). Then there is a change at A11, so I would like Excel to reference (ROWS(B$11:B11), (ROWS(B$11:B12), etc€¦down to the next change. Does this make sense? Is it possible? Thanks, Ryan-- "ryguy7272" wrote: The code looks like this: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop I was trying to get the part: R10C of ROWS(R10C[-1]:RC[-1]) to be absolute if there is a change in values in Column A (which is evaluated by the following) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) I can't think of a way to get the R10C to be absolute and then change once 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. Regards, Ryan--- -- RyGuy "OssieMac" wrote: Hi Ryan, I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Absolute Reference to the Left Until Change in Row
Well, thanks to a huge push from Tom Ogilvy, I finally got this darned thing
resolved. For the benefit of others who encounter a similar challenge follow these (simple) instructions: Data (in my case names) is listed in Column A. Column B contains the following function: =COUNT(SEARCH($A2,'Import Sheet'!$A$1:$A$65000)) C+S+E Column C contains the following function (or versions of the function, based on the macro below): =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)),"") C+S+E Finally, Column D contains this function: =INDIRECT("'Import Sheet'!"&C2) Not C+S+E Cell B1 contains this function: =COUNT(B2:B65536) Cell T1 contains this function: =LOOKUP(10^10,B2:B500)-1 The entire macro looks like this: Sub InsertVarRows() Dim myRow As Long Dim rng As Range Dim sngStart As Double sngStart = Now Range("B2").Select Selection.FormulaArray = _ "=IF(RC[-1]<"""",COUNT(SEARCH(RC1,'Import Sheet'!R1C1:R65000C1)),"""")" Dim LastRow As Long 'Fills down, based on data in column to left With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow) End With Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select lastcell = Cells(Rows.Count, "A").End(xlUp).Row myRow = 2 Do Until myRow = lastcell For i = 2 To Cells(myRow, 2) 'start counter at 2 If Cells(myRow, 1) < "" Then Cells(myRow + 1, 1).Select 'Selection.Insert shift:=xlDown Selection.EntireRow.Insert shift:=xlDown End If Next lastcell = Cells(Rows.Count, "A").End(xlUp).Row myRow = myRow + 1 Loop Range("B2").Select Selection.FormulaArray = _ "=IF(RC[-1]<"""",COUNT(SEARCH(RC1,'Import Sheet'!R1C1:R65000C1)),"""")" With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow) End With Dim LastRow1 As Long LastRow1 = Range("T1") Range("B500").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select Range(ActiveCell, ActiveCell.Offset(LastRow1, 0)).Select Selection.FormulaR1C1 = "=R[-1]C" Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Range("A2:A" & Last).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" Dim iStart As Long Dim sFormula As String Dim iLastRow As Long Dim j 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 j = 2 To iLastRow If Cells(j, 2) = Cells(j - 1, 2) Then .Cells(j, "C").FormulaArray = Replace(sFormula, "<row", j - 1) .Cells(j, "C").FillDown Else .Cells(j, "C").FormulaArray = Replace(sFormula, "<row", j) End If Next End With €˜The part that I had trouble with is: €˜From: Dim iStart As Long €˜To: End With €˜This thing was a pain-in-the-butt!!! Range("D2").Select ActiveCell.FormulaR1C1 = "=INDIRECT(""'Import Sheet'!""&RC[-1])" Dim LastRow3 As Long 'Fills down, based on data in column to left With ActiveSheet LastRow3 = .Cells(.Rows.Count, "C").End(xlUp).Row .Range("D2").AutoFill Destination:=.Range("D2:D" & LastRow3) End With MsgBox "Process Complete!! " & Counter & _ " File Updated!" & vbNewLine & _ " took " & Format(Now - sngStart, "hh:mm:ss") End Sub Thanks to all who helped out!!! Thanks 1,000,000*! Ryan-- "RyGuy" wrote: Thanks for the help OssieMac. I think this is a bit more related to programming than it is related to functions. I think I will try a similar post in the programming DG. In the meantime, I will continue to work out a solution by myself. If I can get something workable, I will post back here for closure. Regards, Ryan-- "OssieMac" wrote: Hi Ryan, I'm sorry but I don't have the answers to this. Perhaps someone else can help. Regards, OssieMac "RyGuy" wrote: I made a little progress b/w last night and today. I believe the problem is that I need to refer to a cell, such as this: KeyCell = ActiveCell.Address(False, True) I found the code on this DG. I guess this makes the Row absolute and the Column relative. This is great, now I need to add this to part of my code: In the second part of the loop, if the values in Column A evaluate to < prior cell, I would like to replace this: ROWS(R2C[-1]:RC[-1]) With this: KeyCell = ActiveCell.Address(False, True) Key Cell = ROWS(R2C[-1]:RC[-1) < -- somehow the rows have to be an absolute reference, until there is a change in Column A The entire loop is shown below: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop The ultimate objective is to get Excel to give me an absolute reference in Cell C2 (B2 is being referenced), such as (ROWS(B$2:B2), (ROWS(B$2:B3), (ROWS(B$2:B4), down to the change in value in Column A, which could be anywhere, but in my current scenario, it is in A10. When I get to A10, I would like Excel to change the reference to (ROWS(B$10:B10). Then there is a change at A11, so I would like Excel to reference (ROWS(B$11:B11), (ROWS(B$11:B12), etc€¦down to the next change. Does this make sense? Is it possible? Thanks, Ryan-- "ryguy7272" wrote: The code looks like this: Do Until ActiveCell.Offset(0, -1) = Empty If ActiveCell.Offset(0, -1) = ActiveCell.Offset(-1, 1) Then 'And ActiveCell < Empty And ActiveCell.Offset(1) < Empty Then Selection.FormulaArray = _ "=IF(ROWS(R2C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R2C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) Then Selection.FormulaArray = _ "=IF(ROWS(R10C[-1]:RC[-1])<=R1C[-1],""A""&SMALL(IF(ISNUMBER(SEARCH(RC[-2],'Import Sheet'!R1C[-2]:R65000C[-2])),ROW('Import Sheet'!R1C[-2]:R65000C[-2])),ROWS(R10C[-1]:RC[-1])),"""")" ActiveCell.Offset(1, 0).Select End If Loop I was trying to get the part: R10C of ROWS(R10C[-1]:RC[-1]) to be absolute if there is a change in values in Column A (which is evaluated by the following) If ActiveCell.Offset(0, -1) < ActiveCell.Offset(-1, 1) I can't think of a way to get the R10C to be absolute and then change once 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. Regards, Ryan--- -- RyGuy "OssieMac" wrote: Hi Ryan, I can't say that I really understand your question. However, here are some samples of code which might help. If it doesn't, then can you please post a sample of your data and explain:- 1. What cells you are trying to compare. 2. What you want to do if compare is equal. 3. What you want to do if compare is not equal. Dim rngCell As Range Range("B1").Select 'Becomes the ActiveCell 'Assign cell to a range variable (Don't forget the 'Set' ) Set rngCell = ActiveCell.Offset(0, -1) 'Range("A1") rngCell.Select 'Select cell by range variable 'Compare by using the range variable in lieu of the cell reference If rngCell = ActiveCell.Offset(-1, 1) Then 'do stuff because it is equal Else 'Reassign a new cell to rngCell variable Set rngCell = ActiveCell.Offset(-1, 1) End If Regards, OssieMac |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortcut to change change cell reference to Absolute reference? | Excel Worksheet Functions | |||
| How do I change the default cell reference from absolute to relati | Setting up and Configuration of Excel | |||
| HOw do I change group of cells from absolute reference? | New Users to Excel | |||
| How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) | |||
| How do I change a cell from absolute reference to relative referen | Excel Discussion (Misc queries) | |||
Linear Mode
