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

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

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

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