Defining Range Question?

[Michael168]

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.

 

[Bob Phillips]

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)

 

[J.E. McGimpsey]

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

 

[Tom Ogilvy]

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.

 

[Michael168]

Thank you for your help.

Regards.

*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

 

[Michael168]

Thank you for your help.

Regards.

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

 

[Michael168]

Thank you for your help.

Regards.

*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)

message