vba 宏以读取一张工作表中的单元格，然后在另一张工作表中搜索匹配项并将日期添加到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17745424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to read cells in one sheet and then search for match in another sheet and add date to column
提问by user2599261
I'm trying to write an excel macro that will go down a column of text and then search for each item in the column in another spreadsheet in the same workbook. When the value is found it should put todays date in a specific column e.g. column H. I have found some code that will do the first part and another piece of code that will do the second part. I just haven't got the knowledge of coding to put the two together and make it work. The two pieces of code are shown below.
我正在尝试编写一个 excel 宏，它将沿着一列文本向下搜索，然后在同一工作簿的另一个电子表格中搜索该列中的每个项目。找到该值后，它应该将今天的日期放在特定的列中，例如 H 列。我找到了一些代码可以执行第一部分，而另一段代码可以执行第二部分。我只是没有获得将两者结合起来并使其工作的编码知识。两段代码如下所示。
Sub From_sheet_make_array()
Dim myarray As Variant
Dim cells As Range
Dim cl
myarray = Range("a1:a10").Value
Set cells = Worksheets("Sheet2").Columns(1).cells
Set cl = cells.cells(1)
Do
If IsError(Application.Match(cl.Value, myarray, False)) Then
Exit Sub
Else:
i = i + 1
'This shows each item in column in messagebox
'Need to pass this value to other code somehow
MsgBox (cl.Value)
End If
Set cl = cl.Offset(1, 0) Loop While Not IsEmpty(cl.Value) End Sub
Set cl = cl.Offset(1, 0) Loop While Not IsEmpty(cl.Value) End Sub
Sub Searchlist()
Dim c As Range
With ActiveWorkbook.Sheets("Sheet1")
' This will search for whatever is in ""
' Somehow need to get cl.Value (myarray) in here
Set c = .Columns("A").Find(What:="text", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
c.Offset(0, 8).Value = Date
End With
Exit Sub
End Sub
结束子
If anyone can stitch them together or point me in the right direction of how to go about it would really help me out. I could be way off as I am very new to this. Thanks for any help offered.
如果有人可以将它们拼接在一起或为我指明正确的方向，那将真正帮助我。我可能会离开，因为我对此很陌生。感谢您提供的任何帮助。
回答by Derek Cheng
From my understanding you're in a process of checking if you have the entry for today in another worksheet and if you do, you put a date-stamp in your current worksheet. Try this: (let me know if it works!)
根据我的理解，您正在检查另一个工作表中是否有今天的条目，如果有，则在当前工作表中放置一个日期戳。试试这个：（让我知道它是否有效！）
Sub DateStampIfFound()
Dim cell As Range
Dim temp As Range
For Each cell In Sheets("worksheet_containing_search_criteria").UsedRange.Columns("Specify_the_column").Cells
'so you dont search for blanks and skipping header row
If cell <> "" and cell.row<>1 Then
Set temp = Sheets("worksheet_where_you_want_the_find_to_happen").Columns("Specify_the_column").Find(What:=cell.Value, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
'if found
If Not temp Is Nothing Then
'if the search_criteria is in the same sheet
cell.Offset(0, number_of_columns_offset_from_cell) = Date
End If
End If
Next
End Sub
结束子
