Why there is an error occuring with find function (through other lists)

Question

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

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

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)

You will need to check if the value is null. If it is null give an error if it has a value do the script. — Mo D Genesis, Jul 01 '19 at 14:40
Well, As I said already, it somehow doesn't find the value even though it is there :/. And that's the main problem I am struggling right now — Alexey, Jul 01 '19 at 14:55
Why are you searching in Rows(2)? Is the value you want in the second row? — AAA, Jul 01 '19 at 14:57
I am not sure but maybe Col = wb.Worksheets("Calculation").Rows(2).Column.Find("31.12.2018") or Col = wb.Worksheets("Calculation").Rows(2).Find("31.12.2018") Does the second one give you any results? — Mo D Genesis, Jul 01 '19 at 14:58
https://stackoverflow.com/a/10952705/5517161 maybe this will help you? — Mo D Genesis, Jul 01 '19 at 15:01
@Alexey, try `Col = wb.Worksheets("Calculation").UsedRange.Find("31.12.2018").Column` — AAA, Jul 01 '19 at 15:04
a) Run a manual find in your worksheet and see if it finds your text. If it does then there is a problem with your code, else there is a problem with the text you are trying to find. b) When using `.Find()` it is preferred to explicitly define the `.LookAt` parameter too, read here for more information: https://learn.microsoft.com/en-us/office/vba/api/excel.range.find — Dean, Jul 01 '19 at 15:05
`Find` returns a `Range` or a `Nothing`. You should **ALWAYS** test for `Nothing`ness, as otherwise there will always be an error when the value is not found. If row 2 contains *date* values formatted like `DD.MM.YYYY` then `Find` is likely to fail as date values are stored as numerics, not strings. — David Zemens, Jul 01 '19 at 15:18

score 1 · Answer 1 · answered Jul 01 '19 at 15:23

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.

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

Why there is an error occuring with find function (through other lists)

1 Answers1

Linked