Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . Thanks for helping. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
Michael,
cRowLast = Cells(Rows.Count,"C").End(xlUp).Row Set testRange = Range("C8:C" & cRowLast) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Michael168" wrote in message ... How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . Thanks for helping. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
One way:
Const FINDSTR As String = "Record" Dim found As Range With ActiveSheet Set found = Intersect(.UsedRange, _ .Range("C8:J" & Rows.Count)).Find( _ What:=FINDSTR, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) End With If Not found Is Nothing Then MsgBox "Found " & FINDSTR & " At " & found.Address Else MsgBox "Did not find " & FINDSTR End If In article , Michael168 wrote: How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
A slightly different interpretation.
Dim rng as Range, rng1 as Range, rng2 as Range With worksheets("Record") set rng2 = .Range(.Range("A1"),.UsedRange) set rng = Intersect(rng2,.Range("C:J")) set rng = rng.offset(7).Resize(rng.rows.count-7) End with set rng1 = rng.Find(What:="Data", . . . ) if not rng1 is nothing then rng1.Select End if Replace "Data" with the string/value you are searching for. Add appropriate arguments to the Find method. -- Regards, Tom Ogilvy Michael168 wrote in message ... How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . Thanks for helping. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
Thank you for your help. Regards. J.E. McGimpsey wrote: *One way: Const FINDSTR As String = "Record" Dim found As Range With ActiveSheet Set found = Intersect(.UsedRange, _ .Range("C8:J" & Rows.Count)).Find( _ What:=FINDSTR, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) End With If Not found Is Nothing Then MsgBox "Found " & FINDSTR & " At " & found.Address Else MsgBox "Did not find " & FINDSTR End If In article , Michael168 wrote: How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . * ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
Thank you for your help. Regards. Tom Ogilvy wrote: *A slightly different interpretation. Dim rng as Range, rng1 as Range, rng2 as Range With worksheets("Record") set rng2 = .Range(.Range("A1"),.UsedRange) set rng = Intersect(rng2,.Range("C:J")) set rng = rng.offset(7).Resize(rng.rows.count-7) End with set rng1 = rng.Find(What:="Data", . . . ) if not rng1 is nothing then rng1.Select End if Replace "Data" with the string/value you are searching for. Add appropriate arguments to the Find method. -- Regards, Tom Ogilvy Michael168 wrote in message ... How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . Thanks for helping. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ * ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining Range Question?
Thank you for your help. Regards. Bob Phillips wrote: *Michael, cRowLast = Cells(Rows.Count,"C").End(xlUp).Row Set testRange = Range("C8:C" & cRowLast) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Michael168" wrote in message ... How to define the range in vba? I want to search for data in columns "C to J" from row number 8 until the last row of the active worksheet name "Record" . Thanks for helping. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ * ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a Range with Criteria | Excel Discussion (Misc queries) | |||
Defining Range Name | Excel Discussion (Misc queries) | |||
help defining dynamic range | Charts and Charting in Excel | |||
Defining a range | Excel Worksheet Functions | |||
Defining Range | Excel Programming |