无法获取WorksheetFunction类的Match属性 [英] Unable to get Match property of the WorksheetFunction class

问题描述

我想要做的是基于一个单元格 oSht_Input.Cells（Rows，7），我想在另一张Sheet（PeriodSheet）列中找到一个匹配项A，并根据给定的列获取相应的值。

What I'm trying to do is based on a Cell oSht_Input.Cells(Rows, 7) , I want to find a match in another Sheet's (periodSheet) Column A, and get the corresponding value based on a given column.

我尝试将 .Index 和 .Match 来做到这一点。 .Index 适用于我，但是我收到和错误说

I have tried combining the usage of .Index and .Match to do this. the .Index works for me, however I'm getting and error that says

运行时错误'1004'：无法获取WorksheetFunction类的Match属性。

Run-time error '1004': Unable to get the Match property of the WorksheetFunction class.

我也尝试过 Application.Match 中的代码，但这会给我一个＃N / A 的价值。

I have also tried doing Application.Match in the codes, but that would return me a #N/A Value instead.

我做错了什么？

我有点困惑使用 Application.Match 和应用程序。 WorksheetFunction.Match 。

Set oSht_Input = Worksheets(outSheet)
Set periodSheet = Worksheets("PeriodMetadata")
lastRow = oSht_Input.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For Rows = 2 To lastRow
    With Application.WorksheetFunction
        dateCell = oSht_Input.Cells(Rows, 7)
        If rollupDataFile.GroupByPeriod Like "Week*" Then
            If rollupDataFile.GroupByPeriod Like "*Sunday" Then
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 1)
            ElseIf rollupDataFile.GroupByPeriod Like "*Monday" Then                    
                oSht_Input.Cells(Rows, 16).value = .Index(periodSheet.Range("B:H"), .Match(dateCell, periodSheet.Range("A:A"), 0), 2)

.... code continues

编辑：决定添加一点更多的上下文以更好地澄清基于初始反馈。

Decided to add in a little more context for better clarification based on the initial feedback.

dateCell 将接收单元格的值，这绝对是一个日期值。期刊表中的列A包含从2000年1月1日至2020年12月31日的每个单一日期，基本上涵盖所有可能的日期。您可以放心地认为， dateCell 中的值将在20年以内。

dateCell will take in the value of a cell, which is definitely a date value. The Column A in periodSheet contains EVERY single date from January 1st 2000 to December 31st 2020, essentially covering all possible dates. You can safely assume that the value from the dateCell will be within this range of 20 years.

1。这是oSht_Input，其中列G是日期

1. This is the oSht_Input, where Column G is the date

2。这是PeriodMetadata表，我试图与列A匹配（其中包含在找到基于.index函数的值之前，每年2000-2020年的每个日期

2. This is the PeriodMetadata sheet, where I am trying to match to Column A (which contains every single date of year 2000-2020 before finding the value based on the .index function.

推荐答案

在使用日期时使用 CLng（）或 .Value2 / p>

Use CLng() or .Value2 instead when working with dates:

Sub SO()
'// C3 = "03/02/2015"
'// A1:A14 = "01/02/2015" to "14/02/2015"
'// All cells formatted as dates

'// This will NOT work:
Debug.Print WorksheetFunction.Match(Range("C3").Value, Range("A1:A14"), 0)

'// This WILL work:
Debug.Print WorksheetFunction.Match(Range("C3").Value2, Range("A1:A14"), 0)

'// This WILL also work:
Debug.Print WorksheetFunction.Match(CLng(Range("C3").Value), Range("A1:A14"), 0)

End Sub

因为Excel将日期存储为数字的方式 - 有时必须在VBA

This is because of the way Excel stores dates as numbers - something which sometimes has to be accounted for in VBA

这篇关于无法获取WorksheetFunction类的Match属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！