Finding a range and performing a function

[Carrie_Loos via OfficeKB.com]

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

 

[Rick Rothstein]

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.

 

[Carrie_Loos via OfficeKB.com]

Yes, thanks I will clean this up quite a bit, for instance the copy,selects
etc., when I can figure out how to identify the range. My problem is when I
run the macro it does not go to the range I am calling out, hence placing the
declaration. I did want to post my code becuase of this but I am always asked
to do it, so I did. Still need to figure out how to get the code to go to my
range and not other blanks on the worksheet.....Any ideas?

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