PDA

View Full Version : Get row number from a closed workbook



cisterne
01-17-2019, 03:57 AM
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
01-17-2019, 04:38 AM
>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
01-17-2019, 05:03 AM
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
01-17-2019, 05:57 AM
>Where to put my searchstring and how do I get the result

column-A : searchstring
column-B : result

cisterne
01-17-2019, 06:20 AM
I think i didnt make myself clear enough

Get row number from a closed workbookI 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
01-17-2019, 07:07 AM
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
01-17-2019, 01:00 PM
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
01-18-2019, 02:57 AM
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