在一行中查找Date变量并返回Column VBA [英] Find Date variable within a row and return Column VBA

问题描述

我正在寻找在一行中找到一个变量并返回列引用.我到目前为止编写的代码是；

I am looking to find a variable within a row and return the column reference. The code I have written so far is;

Dim VarianceDate As String
VarianceDate = Sheets("Summary").Range("C12").Value


Rows("6").Find(What:=VarianceDate, After:=ActiveCell, LookIn:=xlFormulas _
      , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).Activate

TargetCol = ActiveCell.Column

在此示例中，差异日期为2015年6月1日，但是当我进入VBA中的代码时，它什么也没有返回.除了当我手动搜索时，它会找到正确的单元格.

In this example the Variance date is 01/06/2015, however when I stepinto the code in VBA it returns nothing. Except When I search for it manually It finds the correct cell.

最终，我想使用TargetCol参考来帮助我将正确的数据提取到另一个工作簿中.

Eventually I would like to use the TargetCol reference to help me extract the correct data into another workbook.

任何帮助将不胜感激.

谢谢

推荐答案

您需要提及的是搜索词是一个日期，并且如果activecell不在您的查找范围内，由于包含以下内容，也会引发错误之后:= ActiveCell

You need to mention that the search term is a date and also if the activecell is not in your find range, it will throw an error due to the inclusion of After:=ActiveCell

尝试以下代码

Sub FindDateCol()

    Dim VarianceDate As String: VarianceDate = Sheets("Summary").Range("C12").Value

    Dim TargetCell As Range, TargetCol As Integer
    Set TargetCell = Rows("6").Find(What:=CDate(VarianceDate), LookIn:=xlFormulas, LookAt:=xlPart)
    If Not TargetCell Is Nothing Then TargetCol = TargetCell.Column

    MsgBox TargetCol

End Sub

这篇关于在一行中查找Date变量并返回Column VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！