I didn't look at your code in detail, but here is what I observed, see if it
helps you any. First, you do not have to Dim a CID variable since you never
make use of it (the "CID" argument to the Range function is a string value,
not a Range variable). Second, don't use ActiveCell and Selection... you are
inside a For..Each loop, us the loop variable (cell, in this case, which you
didn't Dim by the way). The cell variable is a direct reference to each cell
in the range as the loop iterates the range; so, if the range were, as an
example, A1:B3, then in the first loop, the cell variable would reference
A1, in the second loop, the cell variable would reference B1, in the third
loop, the cell variable would reference A2, and so on... the ActiveCell and
Selection never changes during this loop.
--
Rick (MVP - Excel)
"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:a7e7b7a635757@uwe...
Hi - I have a spreadsheet with several columns with dynamic ranges.
Dynamic
CID range example: =Log!$N$5:INDEX(Log!$N:$N,SerialNbrs). The worksheet
will
be updated each day, too big to keep formula's in it. So I want to find
each
range that I have created, if a cell is empty, update it with the formula
and
move down the range until the end of the range then move on to the next
range.
I'm really stuck trying to get the macro to identify the range. Here is
the
beginning....Help!
Sub FindNewInfo()
Dim CID As Range
For Each cell In Range("CID")
If IsEmpty(cell) Then
ActiveCell.FormulaR1C1 = _
"=INDEX('Daily Data'!C[-13]:C[-9],MATCH(Log!RC[-1],'Daily
Data'!C[-13]
:C[-9],0),5)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=
_
False, SearchFormat:=False).Activate
End If
Next
End Sub
--
Message posted via http://www.officekb.com