为什么查找功能发生错误(通过其他列表) [英] Why there is an error occuring with find function (through other lists)

问题描述

我有一个程序，该程序应该从其他工作簿中复制一些值，并且最初将其设置为从特定销售中获取值.

I have a program that is supposed to copy some values from other workbooks and intially I set it up to take values from the specific sell.

现在，我正在尝试实现搜索功能，而不是手动插入列号.

For now I am trying to implement search function, not to inster the number of a column manually.

我正在尝试使用以下功能

I am trying to use the following functions

Col = wb.Worksheets("Calculation").Rows(2).Find("31.12.2018").Column

但是，即使文件中有一个特定的值31.12.2018，它仍然无法正常工作.

But even though, there is a specific value of 31.12.2018 in the file, it is still not working properly.

此外，它还是以某种方式起作用，直到我更改了某些内容并暂时找不到错误为止.还有一个问题，如果脚本找不到数据，则会导致无法定义的对象错误(似乎在找不到信息时，您无法分配.column).

Also, it was somehow working, until I changed something and can't find the mistake for now. There is a problem as well, if the script doesn't find the data it goes to an error of undefinable object (seems like you can't assign .column, when there is no info found).

strFilter = "Excel Files (*.xls;*.xlsb;*.xlsx),*.xls;*xlsb;*.xlsx"
strTitle = "Select input file (XXXX)"
arrfiles = Application.GetOpenFilename(strFilter, 2, strTitle, , True)
If VarType(arrfiles) = vbBoolean Then Exit Sub

Dim out As String
out = Cells(2, 7)
brow = Cells(2, 8)
bcol = Cells(2, 9)
Dim Target As Range
Dim Source As Range
Dim Col As Integer

For b = 1 To UBound(arrfiles)
Set wb = Workbooks.Open(Filename:=arrfiles(b), UpdateLinks:=False)

lLastRow = wb.Worksheets("Calculation").Cells(Rows.Count, 3).End(xlUp).Row 

ThisWorkbook.Worksheets(out).Cells(brow, 1) = wb.Worksheets("Template").Cells(3, 3) 
ThisWorkbook.Worksheets(out).Cells(brow, 2) = wb.Worksheets("Template").Cells(2, 3) 

lLastRow = wb.Worksheets("Calculation").Cells(Rows.Count, 3).End(xlUp).Row
lLastRowCol = ThisWorkbook.Worksheets("Build").Cells(Rows.Count,1).End(xlUp).Row

For k = 2 To lLastRowCol

  ThisWorkbook.Worksheets(out).Cells(brow, 1) = wb.Worksheets("Template").Cells(3, 3) 
  ThisWorkbook.Worksheets(out).Cells(brow, 2) = wb.Worksheets("Template").Cells(2, 3) 
  Col = wb.Worksheets("Calculation").Rows(2).Find("31.12.2018").Column

  ThisWorkbook.Worksheets(out).Cells(brow, 3) = wb.Worksheets("Calculation").Cells(2, Col)

  Set Target =ThisWorkbook.Worksheets(out).Range(ThisWorkbook.Worksheets(out).Cells(brow, 4), 

  ThisWorkbook.Worksheets(out).Cells(brow, 4 + lLastRow - 4))
  Set Source = wb.Worksheets("Calculation").Range(wb.Worksheets("Calculation").Cells(4, Col), wb.Worksheets("Calculation").Cells(lLastRow, Col))
  Source.Copy
  Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
  brow = brow + 1

Next k

对象未定义是最常见的错误，对未找到的值进行文件跟踪也不起作用(在上面的代码中未提供)

Object undefined is the most common mistake, and also filetring for non-found values is also not working (not presented in a code above)

推荐答案

假定我们正在处理日期(而不是处理看起来像日期的 strings ):您在工作表中看到的只是日期的表示形式(由该单元格的数字格式定义).无论您如何显示日期，它的内部值(双精度)始终是相同的.

Assuming we are dealing with dates (and not with strings that look somehow like as date): What you see in your sheet is just the representation of a date (as defined with the number format of that cell). No matter how you display a date, it's internal value (a double) is always the same.

当您不使用美国设置时，使用 Find -函数作为日期是很棘手的.最好的选择是通过将搜索条件转换为日期来搜索日期的内部编号.请注意，我将您的 Find -命令拆分为两个语句:代码更具可读性，并且调试起来也容易得多.

It is tricky to use the Find-Function for dates when you are not using US-settings. Best bet is to search for the internal number of the date by converting your search criteria to a date. Note that I split your Find-command into two statements: Code is more readable and it's much easier to debug.

Dim searchFor As Date
searchFor = DateValue("16.05.2019")   ' Method 1
searchFor = DateSerial(2019, 5, 16)   ' Method 2

dim resultRange as range, col as long
set resultRange = wb.Worksheets("Calculation").Rows(2).Find(searchFor)
if not resultRange is nothing then
    col = resultRange.Column
End If

这篇关于为什么查找功能发生错误(通过其他列表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！