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/

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/

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" .

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/

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/

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/

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/