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