VBA Match Month & Year in Row to Month & Year from another cell

[mccnap]

Thanks in advance for any help!
I'm trying to locate the month and year contained in a row of one worksheet that matches the month and year contained in a cell on a different worksheet. Then copy the matching column to a third worksheet. With the below, I'm receiving a "Type Mismatch" error on the line "If Month(Sheet6....". I'm not certain this is the most effective way to write this so I'm open to suggestions.

Sub FindDate()

'returns the month and year entered for the project review cycle

Dim a As Integer
Dim b As Integer
Dim c As Long
Dim d As Long

a = Month(Sheet8.Cells(2, 3))

b = Year(Sheet8.Cells(2, 3))


'MsgBox a & "-" & b


c = Sheet6.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
For i = 1 To c

If Month(Sheet6.Cells(1, i)) = a And Year(Sheet6.Cells(1, i)) = b Then
Sheet6.Columns(i).copy

d = Sheet5.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
Sheet5.Cells(1, d + 1).PasteSpecial xlPasteValues
Sheet5.Cells(1, d + 1).PasteSpecial xlPasteFormats
End If

Next

Application.CutCopyMode = False


End Sub

 

[rondeondo]

Hi M
I like to use "For each c in range.cells" type loops, then you can refer to the cell value and properties in the loop without the rest of the reference. this may help with the error your getting. Also range() seems to help when referring to a cell or range of cells