Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Row in Named Range
What is the simplest syntax to use to accomplish the following?:
For Each Row in [NamedRange] If the value in column B = 1 and the value in column D <= [SomeValue] Then .. .. .. End If Next Row Thanks, -- Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Row in Named Range
Essentially, you'll want to do something like
Dim R As Range For Each R In Range("MyRange") If R.EntireRow.Cells(1, "B").Value = 1 And _ R.EntireRow.Cells(1, "D").Value = SomeValue Then ' do something End If Next R HOWEVER.... if that "do something" is to delete a row, you need to work from the bottom of the range upwards to the top of the range. Otherwise you'll end up skipping rows: For deleting, use Dim RNdx As Long With Range("MyRange") For RNdx = .Cells(.Cells.Count).Row To .Cells(1, 1).Row Step -1 If Cells(RNdx, "B").Value = 1 And _ Cells(1, "D").Value = "SomeValue" Then Rows(RNdx).Delete End If Next RNdx End With Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 20 Aug 2009 10:49:01 -0700, wpiet wrote: What is the simplest syntax to use to accomplish the following?: For Each Row in [NamedRange] If the value in column B = 1 and the value in column D <= [SomeValue] Then . . . End If Next Row Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Row in Named Range
For Each mRow In Range(" NamedRange").Rows
If Cells(mRow.Row, "B").value -= 1 And Cells(mRow.Row, "D") <= somevalue Then ... End If Next mRow -- __________________________________ HTH Bob "wpiet" wrote in message ... What is the simplest syntax to use to accomplish the following?: For Each Row in [NamedRange] If the value in column B = 1 and the value in column D <= [SomeValue] Then . . . End If Next Row Thanks, -- Will |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Row in Named Range
Will,
Dim myRow As Range For Each myRow In Range("Fred").Rows If Intersect(Range("B:B"), myRow).Value = 1 And _ Intersect(Range("D:D"), myRow).Value <= 5 Then MsgBox "Hello from row " & myRow.Row End If Next myRow HTH, Bernie MS Excel MVP "wpiet" wrote in message ... What is the simplest syntax to use to accomplish the following?: For Each Row in [NamedRange] If the value in column B = 1 and the value in column D <= [SomeValue] Then . . . End If Next Row Thanks, -- Will |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Row in Named Range
wpiet;459612 Wrote: What is the simplest syntax to use to accomplish the following?: For Each Row in [NamedRange] If the value in column B = 1 and the value in column D <= [SomeValue] Then .. .. .. End If Next Row Thanks, -- Will Assuming th active sheet is the one in question, either: For Each cll In Intersect(Range("NamedRange"), Columns("B")) If cll.Value = 1 And cll.Offset(, 2) <= Somevalue Then '.. '.. End If Next cll orFor Each rw In Range("NamedRange").Rows If Cells(rw.Row, 2) = 1 And Cells(rw.Row, 4) <= Somevalue Then '.. '.. End If Next rw and lots more.. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127188 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming |