I suspect that you'll be doing this not for just one cell (Sheets("Sheet2").Cells(2, 4)), but for many, perhaps cells(D2:D
n)?
If this is the case, then it might be an idea to set up a lookup table once, to be used over and over again, to determine the column you want to place data in:
Code:
Sub blah()
'Set up a lookup table once only from the top row of "Sheet1":
mylookup = Sheets("Sheet1").Range("A1:J1").Value
For i = 1 To UBound(mylookup, 2)
If IsDate(mylookup(1, i)) Then mylookup(1, i) = Format(mylookup(1, i), "yyyy-m")
Next i
'then presumably below here you're iterating through multiple cells, eg.:
For Each cll In Sheets("Sheet2").Range("D2:D5").Cells
colum = Application.Match(Format(cll.Value, "yyyy-m"), mylookup, 0) 'colum is the column number on "Sheet1", and can be used:
If Not IsError(colum) Then Application.Goto Sheets("Sheet1").Cells(1, colum)
Next cll
End Sub
But…
it looks as though you're building a new table from another, in which case it
might be faster/easier/more robust to do this with a pivot table.
If you supply a file with more data and a sheet with the kind of results you're looking for, we should be able to show you how.