Hi all,
I'm having an issue with my VBA code not "finding" the match in the correct column.
When I use the formula =MATCH(MID(Sheet3!A4,13,10)*1,A1:AE1,0)
it gives 2/1/2016 to match the header 2/1/2016, it gets me the value 21 which is Column V on my sheet. This is correct. I'm after the Column number 21 to use in my VBA further along down the line.
The problem is, I'm getting a Run-time error '1004' [Unable to get the Match Property of the WorksheetFuction class] as shown below. or I get Run-time error '13' Type Mismatch if I insert the *1 in Format(Mid(sh3.Range("A4") * 1, 13, 10), "m/d/yyyy")
To recreate the problem, Sheet1'! has Headers columns A1 thru AZ that are 6/1/2014, 7/1/2014, 8/1/2014 ... 12/1/2016, 1/1/2017 etc. Sheet3'!A4 has: Start Date: 2/1/2016
<tbody>
</tbody>
<tbody>
</tbody>
Thanks for any assistance.
I'm having an issue with my VBA code not "finding" the match in the correct column.
When I use the formula =MATCH(MID(Sheet3!A4,13,10)*1,A1:AE1,0)
it gives 2/1/2016 to match the header 2/1/2016, it gets me the value 21 which is Column V on my sheet. This is correct. I'm after the Column number 21 to use in my VBA further along down the line.
The problem is, I'm getting a Run-time error '1004' [Unable to get the Match Property of the WorksheetFuction class] as shown below. or I get Run-time error '13' Type Mismatch if I insert the *1 in Format(Mid(sh3.Range("A4") * 1, 13, 10), "m/d/yyyy")
Code:
Dim sh1 As Worksheet
Dim sh3 As Worksheet
Dim rowDt As String
Dim ColNum As String
Set sh1 = Sheets("Sheet1")
Set sh3 = Sheets("Sheet3")
rowDt = Format(Mid(sh3.Range("A4"), 13, 10), "m/d/yyyy") 'Sheet 3 has [B]Start Date: 2/1/2016[/B] which the date is extracted.
ColNum = Application.WorksheetFunction.Match([B]rowDt[/B], sh1.Range("A1:AZ1"), 0) '
To recreate the problem, Sheet1'! has Headers columns A1 thru AZ that are 6/1/2014, 7/1/2014, 8/1/2014 ... 12/1/2016, 1/1/2017 etc. Sheet3'!A4 has: Start Date: 2/1/2016
Sheet1 | A | B | C | ... | T | U | V | ... |
1 | 6/1/2014 | 7/1/2014 | 8/1/2014 | ... | 1/1/2016 | 2/1/2016 | 3/1/2016 | ... |
<tbody>
</tbody>
Sheet3 | A | B |
1 | ||
2 | ||
3 | ||
4 | Start Date: 2/1/2016 |
<tbody>
</tbody>
Thanks for any assistance.