.Find method for horizontally contiguous cells 1

[MattHenderson]

I am having a great deal of trouble in writing macro code in MS Excel for this purpose.

I have a workbook that comprises 13 sheets. The first is an input sheet that records the details of activity for a particular section or team in my organisation. There are two key cells which form a link to the other 12 sheets.

The 12 other sheets are monthly lists which capture the data from the the first worksheet via a command button & macro placed on the Input sheet

I want my macro to search the first two columsn of 12 monthly sheets using a vertical range of, at maximum, 65 rows for one value, the Date, and once the correct date has been found to then look to the value in the cell immediately to the right in the next column (Shift period) for the second value, a set of numbers.

Then I want to select the entire row in which both values have been located so that I can then populate the rest of the columns with all of the other data from the Input form

There will only ever be two dates in the Date column for each day of the month as the numbers on the other column corresponds to a pattern of attendance for staff at my organisation (there are two shift periods)

If that sounds confusing... wait until you see my code !!

Private Sub CommandButton1_Click()

Dim myDate, myMonth, myShiftTime, myArray

Dim Rfound As Range

Dim iLoop As Integer

myDate = Worksheets("Input").Range("Date")

myMonth = Month(myDate)

myShiftTime = Worksheets("Input").Range("ShiftTime")

myArray = Array("C2:D2", "C10", "C20", "C33", "C34", "J3", "J4", "J11", "I22", "J15", "J16", "J17", "I30")


    If myMonth = 1 Then
        Worksheets("January").Activate
    ElseIf myMonth = 2 Then
        Worksheets("February").Activate
    ElseIf myMonth = 3 Then
        Worksheets("March").Activate
    ElseIf myMonth = 4 Then
        Worksheets("April").Activate
    ElseIf myMonth = 5 Then
        Worksheets("May").Activate
    ElseIf myMonth = 6 Then
        Worksheets("June").Activate
    ElseIf myMonth = 7 Then
        Worksheets("July").Activate
    ElseIf myMonth = 8 Then
        Worksheets("August").Activate
    ElseIf myMonth = 9 Then
        Worksheets("September").Activate
    ElseIf myMonth = 10 Then
        Worksheets("October").Activate
    ElseIf myMonth = 11 Then
        Worksheets("November").Activate
    ElseIf myMonth = 12 Then
        Worksheets("December").Activate

End If

With ActiveSheet.Range("A4:A65")
Set Rfound = Range("A4")
For iLoop = 1 To 61
Set Rfound = Range("A4:A65").Find(What:=myDate, After:=Rfound, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If ActiveCell.Offset(0, 1) = myShiftTime Then
Rfound.EntireRow.Select
Selection.Activate

Exit For
End If
Next iLoop
End With

End Sub

When this macro is run I'm getting a 'type mismatch error' which highlights this block of code (the .find part)

Set Rfound = Range("A4:A65").Find(What:=myDate, After:=Rfound, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

What am I doing wrong ?

Thanks in advance for you time.

Matt Henderson

 

[kevinclark]

Hi Matt

I am wondering if in place of your With Block of code, the following would work for you ?


For Each objCell In ActiveSheet.Range("A4:A61&quot
If objCell.Value = MyDate Then
If objCell.Offset(0, 1).Value = myShiftTime Then
objCell.EntireRow.Select
Exit For
End If
End If
Next objCell

 

[MattHenderson]

Many thanks for that Kevin.

It works beautifully!

Kind Regards

Matt H