Help With Find Method

[hascons]

Hello,

I have an excel list with (column A Blank) (Column B Date) (Column C Time) (Column D Activity) I'm trying to use the find method to copy the activity to column F At a time that is selected on another worksheet.

Here is the Code:

Sub Test1()

Dim t As Date
Dim r As Range
Dim lr As Long

lr = Sheets("test").Range("c2").CurrentRegion.Rows.Count

t = Sheets("Rules").Range("E6").Value

Sheets("Test").Select
Range("c2").Select


'Set r = Range(Cells(2, 3), Cells(lr, 3)).Find(What:=CDate(t), Lookat:=xlWhole, LookIn:=xlValues)
'r.Offset(, 6).Value = r.Offset(, 1).Value

End Sub

I get an error code 91 at the last line

r.offset(, 6).Value = r.Offset(, 1).value

Thanks for any help

Steve

 

[HaHoBe]

Hi, Steve,

I´d prefer

lr = Sheets("test").Cells(Rows.Count, "C").End(xlUp).Row

Are you sure about your offsets (Time being found in C and targetcolumn being F means an offset of 3 for me)

Sub Test1()

Dim r As Range
Dim lr As Long

lr = Sheets("test").Cells(Rows.Count, "C").End(xlUp).Row

Sheets("Test").Select
Range("c2").Select

Set r = Range(Cells(2, 3), Cells(lr, 3)).Find(What:=Sheets("Rules").Range("E6").Text)
r.Offset(, 3).Value = r.Offset(, 1).Value

End Sub

Ciao,
Holger

 

[chergh]

Try:

Sub Test1()

Dim t As Date
Dim r As Range
Dim lr As Long
dim ws as worksheet

lr = Sheets("test").Range("c2").CurrentRegion.Rows.Coun t

t = Sheets("Rules").Range("E6").Value

Sheets("Test").Select
Range("c2").Select
set ws = thisworkbook.worksheets("Test")

Set r = ws.Range(ws.Cells(2, 3), ws.Cells(lr, 3)).Find(What:=CDate(t), Lookat:=xlWhole, LookIn:=xlValues)
r.Offset(, 6).Value = r.Offset(, 1).Value

End Sub

 

[hascons]

Holger,

Thanks for your help This does solve the error problem, However it only performs this task once. My worksheet has several months of daily data and i would like This sub to perform this task for every day (that has Data ). I'm never really used this find method but from what i've read it performs much faster than a Looping Structure. Do I need to perform this find method inside of a Looping Structure or does this work on the Whole range.

Steve

 

[HaHoBe]

Hi, Steve,

from what I understand there are several entries in sheet Rules in Column E to be searched in sheet test Column D. You assigned a range for the Find-Method to earch in, and if the "WHAT" is found a range is assigned. If not the run time error 91 could arise which will be avoided by checking if the range object is Not Nothing (no match found), here no action is taken but you could Debug.Print the WHAT in the VBE or show a MsgBox:

Sub Test2()

Dim r As Range
Dim lr As Long
Dim lngCounter As Long

With Sheets("test")
   lr = .Cells(Rows.Count, "C").End(xlUp).Row
   For lngCounter = 6 To 8
      Set r = .Range(.Cells(2, 3), .Cells(lr, 3)).Find(What:=Sheets("Rules").Range("E" & lngCounter).Text)
      If Not r Is Nothing Then r.Offset(, 3).Value = r.Offset(, 1).Value
   Next lngCounter
End With

Set r = Nothing
End Sub

Ciao,
Holger