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 |
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) |