VBA - 使用搜索功能在两个日期之间设置范围 [英] VBA - Set Range Between Two Dates Using Search Function

问题描述

我正在尝试让VBA代码搜索用户输入的值（在表单上），并根据值设置范围。

我需要代码扫描列，直到它找到值（这是一个日期），然后扫描通过列获得范围的第二部分。我需要这样做，因为可能有同一个日期的多个实例，他们都需要考虑。

我已经尝试过：

  StartRange = ws.Cells.Find（What：= StartDate，SearchOrder：= xlRows，_ 
 SearchDirection：= xlNext，LookIn ：= xlValues）
 
 EndRange = ws.Cells.Find（What：= EndDate，SearchOrder = = xlRows，_ 
 SearchDirection：= xlPrevious，LookIn：= xlValues）
  

但是，这并不能像我预期的那样发生错误。 （编辑：WS已被定义，所以我知道这不是问题）。我甚至不知道我是否正确地走这条路。

我感到失败了：（

任何帮助将不胜感激，谢谢提前！

编辑：

我还没有尝试任何建议，因为我现在远离我的项目，但我觉得我需要澄清一些事情。

1. 日期将始终按时间顺序排列，我有一个脚本，在表单激活下组织它们




2. 我需要能够处理日期的错误没有出现在数据库中，我还需要脚本能够跳过不存在的日期，即第1，第1，第3，第3，第5，如果我的开始和结束日期是第1和第5，整个例子将是范围。

感谢您的帮助， p>

EDIT2：

我已经尝试了几个答案，并将其添加到我的代码中，但现在是失败了e_Global失败。

  Dim startrange，endrange，searchrange As Range 
 LookUpColumn = 2 
 
使用ws.Columns（LookUpColumn）
设置startrange = .Find（什么：= Me.R_Start.Value，_ 
之后：= ws.Cells（.Rows.count，LookUpColumn），_ 
 SearchOrder：= xlRows，_ 
 SearchDirection：= xlNext，LookIn：= xlValues）
 
设置endrange = .Find（What：= Me.R_End.Value，_ 
 After ：= ws.Cells（5，LookUpColumn），_ 
 SearchOrder：= xlRows，_ 
 SearchDirection：= xlPrevious，LookIn：= xlValues）
 
 searchrange = Range（startrange， endrange）
 
 MsgBox searchrange.Address 
结束
  

任何建议？

解决方案

让我们从这开始，看看需要调整的内容。
此代码将寻找一个日期（基于输入），在列中查找该日期的位置。与EndDate相同，然后在2个位置之间的该列上创建一个范围。

  Sub ARange（）
 Dim Sh As Worksheet：Set Sh = Sheets（Sheet1）
 Dim i，j As Integer 
 
 LookupColumn =A'定义LookupColum /如果您发现使用列索引更简单，那么您需要将搜索从（范围）切换到（单元格）
 StartDate_Value = Sh.Range（B2）。值'使用您需要定义输入值的任何值
 EndDate_Value = Sh.Range（C2）。值'使用任何您需要定义输入值
 
对于i = 1至30000 
如果Sh.Range（LookupColumn& i）.Value = EndDate_Value Then EndDate_Row = i 
 Next i 
 
对于j = EndDate_Row到1步-1 
如果Sh.Range（LookupColumn& j）.Value = StartDate_Value然后StartDate_Row = j 
下一步j 
 
 Dim MyDateRange As Range：设置MyDateRange = Sh.Range（LookupColumn& StartDate_Row&：& LookupColumn& EndDate_Row）
 MsgBoxMyDateRange =& LookupColumn& StartDate_Row& ：& LookupColumn& EndDate_Row 
 End Sub 
  

另一种方法应该意味着从底部向上寻找EndDate（如Excel的列值）和StartDate从上到下。像这样：

 对于i = 30000到1 step -1 
对于j = 1到30000 
第三个（魅力）：从上到下为EndDate，从顶部到底部为StartDate。像这样：
 对于i = 1到30000 
对于j = 1到30000 
  
和第四（一）：
 对于i = 1到30000 
对于j = 30000到1步骤-1 
  
在我的家庭笔记本电脑上，30.000个单元格上的搜索是即时（1秒以下）。 
尝试一下，根据反馈我们可以对其进行微调。
 
 
 
另一方面，我可能会看到您的问题，顶部/底部位置之间的所有值，但是任何具有2个输入值之间的日期值的单元格永远不会列出列表（列单元格）中的值排列。即如果StartDate = 1.Jan.2013和EndDate = 3.Jan.2013。代码应该从30.000列表中拿起1,2和3，这不意味着这3个日期的位置（实际上可能会发现数千次）。如果这是真的，解决方案可能比上述方法简单。
 
I'm trying to get my VBA code to search through a column for a user-inputted value (on a form) and set a range based on the values.

I need the code to scan DOWN through the column until it finds the value (which is a date) and then scan UP through the column to get the second part of the range. I need it to be like this because there might be multiple instances of the same date and they all need to be accounted for.

I've tried this:
StartRange = ws.Cells.Find(What:=StartDate, SearchOrder:=xlRows, _
    SearchDirection:=xlNext, LookIn:=xlValues)

EndRange = ws.Cells.Find(What:=EndDate, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues)
But it's not working the way I had expected and is erroring. (Edit: the WS has been defined, so I know that's not the issue). I don't even know if I'm going about this the right way

I'm feeling defeated :(

Any help would be appreciated, Thanks in advance!

Edit:

I've yet to try any of the suggestions as I am away from my project at the moment, but I feel I need to clarify a few things. 

The dates will always be in chronological order, I have a script that organises them on sheet activation
I need to be able to error handle dates that do not appear in the database, I also need the script to be able to "skip over" dates that don't exist. Ie, 1st 1st 1st, 3rd, 3rd, 5th. If my start and end dates were the 1st and 5th, the entire example would be the range. 
Thanks for your help so far guys though, I appreciate it!

EDIT2:

I've tried a few answers and have added this in to my code, but it is now failing on a Range_Global fail.
Dim startrange, endrange, searchrange As Range
LookUpColumn = 2

With ws.Columns(LookUpColumn)
    Set startrange = .Find(What:=Me.R_Start.Value, _
      After:=ws.Cells(.Rows.count, LookUpColumn), _
      SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues)

    Set endrange = .Find(What:=Me.R_End.Value, _
      After:=ws.Cells(5, LookUpColumn), _
      SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues)

    searchrange = Range(startrange, endrange)

MsgBox searchrange.Address
End With
Any suggestions?
解决方案
Let's start with this and see what needs to be fine tuned.
This code will look for a date (based on input) and find the position of that date in a column. Same with the "EndDate" and then creates a range on that column between the 2 positions. 
Sub ARange()
Dim Sh As Worksheet: Set Sh = Sheets("Sheet1")
Dim i, j As Integer

LookupColumn = "A" 'Define the LookupColum / If you find using column index to be simpler then you need to switch the search from (range) to (cells)
StartDate_Value = Sh.Range("B2").Value 'Use whatever you need to define the input values
EndDate_Value = Sh.Range("C2").Value 'Use whatever you need to define the input values

For i = 1 To 30000
    If Sh.Range(LookupColumn & i).Value = EndDate_Value Then EndDate_Row = i
Next i

For j = EndDate_Row To 1 Step -1
    If Sh.Range(LookupColumn & j).Value = StartDate_Value Then StartDate_Row = j
Next j

Dim MyDateRange As Range: Set MyDateRange = Sh.Range(LookupColumn & StartDate_Row & ":" & LookupColumn & EndDate_Row)
MsgBox "MyDateRange = " & LookupColumn & StartDate_Row & ":" & LookupColumn & EndDate_Row
End Sub
Another approach should imply looking for the EndDate from bottom upwards (as in Excel's column values) and for the StartDate from top to bottom. like this:
For i = 30000 to 1 step -1
For j = 1 To 30000
And the 3rd (the charm):for the EndDate from top to bottom and for the StartDate from top to bottom. like this:
For i = 1 to 30000
For j = 1 To 30000
And the 4th (The One):
For i = 1 to 30000
For j = 30000 to 1 Step -1
On my home laptop the search on the 30.000 cells is instant (under 1s).
Give it a try and based on the feedback we can fine tune it.

On the Other hand, I might read your question as for looking To select not all values between the top / bottom position, but any cells with values of dates between the 2 input values neverminind the arrangement of the values within the list (column cells). i.e. If StartDate = 1.Jan.2013 and EndDate = 3.Jan.2013. The code should pick up 1,2 and 3 from the 30.000 list neverminind the position of these 3 dates (which in fact may be found thousands of times). If This is true, the solution may be simpler than the one above.

                        
这篇关于VBA  - 使用搜索功能在两个日期之间设置范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！