Hi,
This assumes your data is sorted ..... abd creates ranges with
names "range_n" where n is a number.

Sub Find_Numbers(FindNum)

Dim FirstRec As Long, NumRecs As Long, lastrow As Long
Dim rnga As Range, rngb As Range

lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Set rnga = Range("c1:C" & lastrow)

FirstRec = Application.Match(FindNum, rnga, 0)
NumRecs = Application.CountIf(rnga, FindNum)

Set rngb = Range("c" & FirstRec & ":c" & NumRecs + FirstRec - 1)

MsgBox rngb.Address

ActiveWorkbook.Names.Add Name:="range_" & FindNum, RefersTo:=rngb

End Sub


Sub Test()
Dim srchNum As Long
srchNum = 2
Call Find_Numbers(srchNum)
End Sub


HTH


"jhahes" wrote:


How do I name a range with an if statement

Say on a worksheet change on sheet4

If I add a record to the database the new record will go into a certain
range.



Here is what i am trying to do

Search thru say Column C

If the search finds a 1 then it would select it or activate it

All the selected fields would be named a range (Range_1)


In my case all records are sorted so all the ones would be consecutive,
but could you do this same scenario with records out of order


I would appreciate any input or help

Thank you for your time

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=378140