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
|