0

The script works fine if i put: ThisWorkbook but with ActiveWorkbook doesn't work.

Error 1004 say: "Unable to get the Match property of the Worksheet function class"

Dim dat As Date

calea_livrat = "my link" & ".xlsx"
Workbooks.Open calea_livrat

With ActiveWorkbook
    dat = zi & "-" & luna & "-" & an
    data_gen = CDbl(dat)
    nr_linie = Application.WorksheetFunction.Match(data_gen, ActiveWorkbook.Worksheets("PE_Centralizare").Range("A:A"), 0)
    MsgBox nr_linie
End With

Something is wrong here: Application.WorksheetFunction.Match(data_gen, ActiveWorkbook.Worksheets("PE_Centralizare").Range("A:A"), 0) but i can't figure out what.

BOB
  • 700
  • 2
  • 16
  • 35
  • 1
    First: Please fix the syntax error in your sample code. Second: What's the purpose of that `With` block? None of your code uses it. Remove it or use use it. – Tomalak Jan 09 '17 at 10:56
  • Maybe look here: http://stackoverflow.com/questions/20214540/application-worksheetfunction-match-method Also, possible duplicate of http://stackoverflow.com/questions/38398310/match-function-in-vba – Veve Jan 09 '17 at 10:59
  • My code, open an excel file and with that file opened i need to `Match` something. That Match doesn't work property – BOB Jan 09 '17 at 11:01
  • @Marius post the rest of your relevant code, maybe we can help you avoid the error there. Yyu are already inside `With ActiveWorkbook`, why do you need `ActiveWorkbook.Worksheets("PE_Centralizare").Range("A:A")` ? why not `.Worksheets("PE_Centralizare").Range("A:A")` ? And last, what do you do in case `Match` function is unable to find a match ? how do you handle this error ? ` – Shai Rado Jan 09 '17 at 11:10
  • @shai-rado The rest of the code is not relevant, everything is work if I put `ThisWorksheet` instead `ActiveWorkbook` or `ActiveSheet` – BOB Jan 09 '17 at 11:13
  • I've tried `.Worksheets("PE_Centralizare").Range("A:A")` and still error – BOB Jan 09 '17 at 11:15

1 Answers1

1

This is not the answer, just how to use Match function in your case properly:

Dim nr_linie As Variant

With ActiveWorkbook
    dat = zi & "-" & luna & "-" & an
    data_gen = CDbl(dat)

    ' in case Match was able to find data_gen in Column A
    If Not IsError(Application.Match(data_gen, .Worksheets("PE_Centralizare").Range("A:A"), 0)) Then
        nr_linie = Application.Match(data_gen, .Worksheets("PE_Centralizare").Range("A:A"), 0)
        MsgBox "Row number " & data_gen & " value was found in row " & nr_linie
    Else ' <-- Macth failed, unable to find find data_gen in Column A
        MsgBox data_gen & " value not found in Range !"
    End If
End With
Shai Rado
  • 33,032
  • 6
  • 29
  • 51