使用 Excel VBA 在整个 ROW 中搜索位于另一个单元格中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21909411/
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
Using Excel VBA to search an entire ROW for a date located in another Cell
提问by Petay87
I am new to both stackoverflow.com and VBA within Excel, so go easy on me :-)
我对 Excel 中的 stackoverflow.com 和 VBA 都不熟悉，所以请放轻松 :-)
I am looking to have a button in my excel sheet that when clicked, will search the entire or row 1 for a date (located in another cell). If it finds the date in a call in row 1, it will enter some text in the cell below it. If it doesn't find the date, it will add the date to the next free cell in the row and then add the text in the cell below that.
我希望在我的 Excel 工作表中有一个按钮，单击该按钮时，将在整个或第 1 行中搜索日期（位于另一个单元格中）。如果它在第 1 行的呼叫中找到日期，它将在其下方的单元格中输入一些文本。如果它没有找到日期，它会将日期添加到行中的下一个空闲单元格，然后在其下方的单元格中添加文本。
I know I have asked a lot and I am happy to accept partial answers as I know how to do some of the aspect of this. For example the pasting of text and such. The part I am finding difficult is the finding on the date in the entire row 1 and then finding the next blank cell if no date is found.
我知道我问了很多，我很高兴接受部分答案，因为我知道如何做这方面的一些事情。例如粘贴文本等。我发现困难的部分是在整个第 1 行中查找日期，然后在未找到日期的情况下查找下一个空白单元格。
Any help or pointers will be highly appreciated!
任何帮助或指示将不胜感激！
However, I would be even happier if I get a response, the person also explains how the code works as I am very keen to learn VBA and use it again in the future and not just copy and paste.
但是，如果我得到回复，我会更高兴，该人还解释了代码的工作原理，因为我非常渴望学习 VBA 并在将来再次使用它，而不仅仅是复制和粘贴。
Thanks in advance for any replies! :-)
提前感谢您的任何答复！:-)
回答by Dmitry Pavliv
Give this a try. I've commented code in details, but if you have some questions, ask in comments:)
试试这个。我已经详细评论了代码，但如果您有任何问题，请在评论中提问:)
Sub test()
Dim ws As Worksheet
Dim rng As Range
Dim targetDate As Range
'change sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")
'change address of your cell with target date
Set targetDate = ws.Range("A4")
'tries to find target date in first row
Set rng = ws.Range("1:1").Find(What:=targetDate, LookAt:=xlWhole, MatchCase:=False)
If rng Is Nothing Then
'if nothing found - search for last non empty column
Set rng = ws.Range("1:1").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
If rng Is Nothing Then
'if row 1 is empty
Set rng = ws.Range("A1")
Else
'take next column after last non empty
Set rng = rng.Offset(, 1)
End If
'write target date
rng = targetDate
rng.NumberFormat = "dd.mm.yyyy"
'write something below the date
rng.Offset(1) = "test"
Else
'if date is found - write something below the date
rng.Offset(1).Value = "test2"
End If
End Sub
