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 search string 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

[Altie]

Hello mana - your post works great for me, but I have one more question to something that I cannot figure out.

In your formula above, how would I pass a variable to the sheet name. In your example the sheet name is "Template_0". I would like to pass a variable called "MachID" to this position, and of course the MachID variable would would have the sheet name definition in it.

Any help would be greatly appreciated!

[Paul_Hossler]

Five year old thread so I suspect the mana is notgoing to see it. Usually it's better to start a new one, and just reference the old one

Anyways, try this. I couldn't test it but an eyeball check looks good

Option Explicit

Sub test3()
     Dim s As String
     Dim f As String
     Dim MachID As String    
     s = "PRO123456"
     MachID = "ABCD1234"    
     f = "=Iferror(match(""" & s & """,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]" & MachID & "'!$A:$A,0),""not found"")"    
     Debug.Print f
End Sub

[Altie]

Paul - that worked great!!! My code looked like yours except...I was missing two pieces of punctuation.

Your GOOD code

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

My BAD code

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

As you can see, I was missing the double-quotes before and after the MachID variable. Arghhhhh!!!!!

Thank you again!

[jindon]

No need to use cell.

    Dim x, s$
    s = "PRO123456"
    x=executeexcel4macro("match(""" & s & """,'Q:\KUNDER\PROJEKTER\HPG\[HPG_DB.xlsx]Template_0'!c1:c1,0)")
    msgbox iif(iserror(x),"not foound",x)

[Altie]

Hello again, Paul (and any others)...

I have a follow up question to the solution you folks have offered, which works great for the issue I was working on at the time. Your solution finds the keyword as long as the keyword is the lone item in a cell in the selected range.

My new question that I have thus far been unable to figure out is: How to search for a keyword in a range of strings in a closed workbook. For example, my closed w/b column B17:B52 contains many lines of text. A simple example below:
Range Text
B17 The quick brown fox
B18 jumps over the lazy dog
B19 and the fat black cat
B20 is nowhere to be found

Let's say I want to search for the keyword "cat". All I'm looking for is a true/false as to whether the keyword appears anywhere in the strings of text found in range B17:B52 of the closed w/b. Also (not sure if this matters or not), range B17:B52 cells are merged cells. In other words, selecting cell B17 selects cells B17:T17, as they are merged.

The simple (and very fast) solution that you had offered to my previous question is SO close to doing what I need to do now, but I'm stuck.

Also, I'm new to this site, so I'm not sure if I should have started a completely new thread or follow up with this one, since it seems so close...

Thank you in advance for any help.

[Aflatoon]

Just add an asterisk to the start and end of the search term. But be aware that that will naturally match "cat" with words like "scatter". In your real data that may or may not be a consideration, depending on what you are looking for.

[Altie]

Aflatoon - wow, you just saved me a ton of time! I didn't realize that the simple asterisk wildcard would work here...oy...


Perhaps you can shed light on one more item for this VB newbie. What is the function of the red 0 that I highlighted in the line below?


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


Thank You again!

[Aflatoon]

That's not a VBA thing. It means that the MATCH function is looking for an exact match. (you have to use that with the wildcard option)

[Altie]

Got it, thank you. And, do you know if the filepath and filename can be variables?

[Altie]

Never mind, I figured it out. It was basically just syntax, as expected...


I did modify the code so that the "Line" variable contains the entire path, filename, and necessary syntax, so it looks like:

Line = MyPath & "[" & myFile & "]" & wsname & "'"

And the final code looks like:

f = "=IfError(match(""" & Text & """, " & Line & "!$B:$B,0),""not found"")"