This month I would like to show you what I call: In-Cell-Lookups. These are the perfect replacement for multiple nested IF functions.

Enter 1 in cell A1

Select A1 and while holding down the Ctrl key and drag down by the Fill Handle until you reach Cell A20.

Now, in B1 add this formula;

**=LOOKUP(A1,{1,6,11,16},{"1-5","6-10","11-15","16-20"})**

and double click the
Fill Handle so this formula is copied down to B20.

As you can see, this returns a text result of the numeric scope our numbers fall
into.

Here's the details of how this works. Text quoted from Excel help

*SYNTAX:=LOOKUP(lookup_value,lookup_vector,result_vector)
*

For both lookup_vector and Result_vector we have used what is known as

After reading the link above you should understand "Array Constants". So, as you can see our "lookup_vector" is placed in ascending order using the lowest value for each numeric scope. Our "result_vector" Array Constants

This Month I would like to show you 2 ways to restrict
looping by using the
COUNTIF Function with the
Find Method. The 1st
code uses a **whole** cell match, while the 2nd uses a **part** cell match.

The key thing to note in both codes is our use of the range
variable rFound in the Find Method parameter for After: That is, **After:=rFound.**
By using this we can move down the Column and find all matches. If we didn't use
this, we always find the 1st match over and over again.

Sub RestrictLoop1WholeCellMatch()Dim rFound As Range Dim lLoop As Long With Range("A:A") 'Set our range variable to the 1st cell in Column A Set rFound = .Cells(1, 1) 'Use COUNTIF to restrict our looping For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "Dave") 'Use the Find Method and set each parameter to suit whole cell match Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) With rFound 'Your .With code here End With Next lLoop End WithEnd Sub

Sub RestrictLoop2PartCellMatch()Dim rFound As Range Dim lLoop As Long With Range("A:A") 'Set our range variable to the 1st cell in Column A Set rFound = .Cells(1, 1) 'Use COUNTIF to restrict our looping For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "*Dave*",) 'Use the Find Method and set each parameter to suit part cell match Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) With rFound 'Your .With code here End With Next lLoop End WithEnd Sub

**
Excel Dashboard
Reports & Excel Dashboard Charts 50% Off**

**
Become an
ExcelUser Affiliate & Earn Money**

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft