Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming ranges with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming ranges with VBA
ActiveWorkbook.Names.Add Name:="rangenamehere", RefersToR1C1:="=SOF!R1C1"
just place that inside an IF....THEN BLock -- When you lose your mind, you free your life. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming ranges with VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming ranges with VBA
Note1: for multiarea ranges the only reliable way to define a name with VBA is setting the name property of the range object ofcourse you could just use a filter.. but this is an alternative and works well PROVIDED you will not find more than 500 or so non-consecutive records.. the union method is very slow on large multiareas. Sub foo() Dim rngCel As Range Dim rngTbl As Range Dim rngSel As Range With ActiveSheet Set rngTbl = Intersect(.Range("C:C"), .UsedRange) End With 'start the union with a cell outside the table Set rngSel = rngTbl.Worksheet.Range("IV1") 'loop the cells For Each rngCel In rngTbl If rngCel.Value = 1 Then Set rngSel = Union(rngSel, rngCel) Next If rngSel.Count = 1 Then MsgBox "No records found" Else 'Remove the "IV1" Set rngSel = Intersect(rngSel, rngTbl) 'Expand to entirerows Set rngSel = rngSel.EntireRow 'Assign the name at worksheetlevel rngSel.Name = "'" & rngSel.Worksheet.Name & "'!myrecords" End If End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
naming ranges | Excel Worksheet Functions | |||
naming ranges | Excel Worksheet Functions | |||
Naming ranges? | Excel Discussion (Misc queries) | |||
Naming Ranges | Excel Discussion (Misc queries) | |||
naming ranges | Excel Programming |