Get row number from a closed workbook

[cisterne]

I am trying to get the row number, where the criteria meets a certain value

I have been trying with this code:

Dim wkb As Workbook
Dim a As Range
    Dim clientwks As Worksheet
    Dim listrange As String
  Dim foundValue As Range
  Dim fundet As Integer
  Dim clientlist As Range
  Dim listen As Range

  Set wkb = Workbooks.Open("Q:\KUNDER\PROJEKTER\HPG\HPG_DB.xlsx")
    Set clientwks = wkb.Sheets("Template_0")
    Set clientlist = clientwks.Range("A1")
    listrange = "PRO123456"
    Set listen = Range("A1", Range("A1").End(xlDown))
For Each a In listen
  Set foundValue = clientlist.Cells.Find(listrange)
  If Not foundValue Is Nothing Then
    a.Offset(0, 0).Value = foundValue.Row
  End If
  Next a

[mana]

＞a.Offset(0, 0).

???

Option Explicit


Sub test()
    Dim f As String
    
    f = "=Iferror(match(A1,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]Template_0'!$A:$A,0),A1)"

    With Range("A1", Range("A1").End(xlDown)).Offset(, 1)
        .Formula = f
        .Value = .Value
    End With
    
End Sub

[cisterne]

f = "=Iferror(match(A1,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]Template_0'!$A:$A,0),A1)"

Where to put my searchstring and how do I get the result

[mana]

>Where to put my searchstring and how do I get the result

column-A : searchstring
column-B : result

[cisterne]

I think i didnt make myself clear enough

Get row number from a closed workbook

I am trying to get the row number, where the criteria meets a certain value


The search string is "PRO123456" and the column to search is "A1" - when it match i would like it to tell me the rownumber

[mana]

Sub test2()
    Dim s As String
    
    s = "PRO123456"
'   Cells(1).Formula = "=Iferror(match(""" & s & """,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]Template_0'!$A:$A,0),""not found"")"

End Sub

[cisterne]

Apparently i cant explain my self

search for s
return the row number where the match is
set a variable equal to the row number

[mana]

Sub test3()
   Dim s As String
    Dim f As String
    Dim n

    s = "PRO123456"
    f = "=Iferror(match(""" & s & """,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]Template_0'!$A:$A,0),""not found"")"

    With Cells(Columns.Count)
        .Formula = f
        n = .Value
        .Clear
    End With
    
    MsgBox "row number : " & n
    
End Sub