Match month in a range of dates

[ansridhar]

I have two sheets. I pick up a date from sheet 1 and want to match the month of this date with month present in range of dates in another sheet 2, placed in Row 1. I want to match the month and pick the column no. I am using this command, but I get error 13, Type Mismatch.

I am using:
lcol = Application.WorksheetFunction.Match(Month(Cells(i, 2).Value), Month(Rows(1)), 0)

Please guide

 

[vletm]

ansridhar
Do You use same syntax as below?
=Match(lookup_value, lookup_array, [match_type])
If yes, then You should able to send a sample Excel-file, which has some sample data?

 

[ansridhar]

I am using lcol as an integer.
I am using the command in VBA. For every row in sheet1, I am picking up the date from column1 in sheet 1 and finding the corresponding column in sheet2, by matching the month, using lcol = Application.WorksheetFunction.Match(Month(Cells(i, 1).Value), Month(Rows(1)), 0).
Hope I have clarified.

 

[Marc L]

As Month(Rows(1)) has no sense so to use with a cell only …​

​

 

[vletm]

ansridhar
Okay ...
You missed Your using code.
You seems to change Your Match ...
Your Sheet1's B-column has dates.
Your Sheet2's the 1st row has dates.
Your Match tries to find Month-value (1..12) from ... Month(Rows(1)) ... hmm?
Have You verified that Month(Rows(1)) could give correct values ... for me - not.
Have You explain somewhere ... which Sheet's Cell(i,1)?
Have You explain somewhere ... which Sheet's Rows(1)?
One of Your challenge is that those are in different sheets.
If You're using Month ... then You could find the 1st Month ... is it okay?
Did You get any ideas?

 

[ansridhar]

Tried to explain what I am doing. Modified my code by specifying sheet extension of other file..Any ideas please

 

[ansridhar]

Want to find the column number of matching month in Row 1 of sheet 2 and sum the amount. I need to tackle this 'Match' problem, Rest I can manage. With 1st date of a month, I am getting col no, using Clng function. But when the date is other than 1st of the month, I need to crack it.

 

[vletm]

ansridhar
You seems to miss ... some replies.
Here one sample.

Sub Do_It()
    With Sheets("Sheet1")
        For a = 2 To 11
            bb = CLng(WorksheetFunction.EoMonth(.Cells(a, "A"), -1) + 1)
            b = WorksheetFunction.Match(bb, Sheets("Sheet2").Range("1:1"), 0)
        Next a
    End With
End Sub

 

[ansridhar]

Thanks. It works. Initially set b, bb as integer, then to long.