So I am getting the error in the title on the AT_cellnum line where I use the address function.
Sub AllTransURL()
Dim AT_rownum As Integer, pathrange As Range, AT_cellnum As String, sheetname As String
sheetname = "All Transmissions"
Set pathrange = ThisWorkbook.Worksheets("All Transmissions").Range("H1:H53")
AT_rownum = Application.WorksheetFunction.Match("infra/remwip/Public/0_00_Rapports", pathrange, 0)
AT_cellnum = Application.WorksheetFunction.Address(AT_rownum, 1, 1, 1, sheetname)
End Sub
Thanks you
There's no need for an Address function in the object model, hence it's not on the WorksheetFunction interface.
Rule of thumb, if you're maknig an early-bound member call and the member you want to invoke isn't showing up in the autocompletion name list, it's not a member of the object you're working with; if the code compiles anyway, there's a good chance it will throw error 438 at run-time.
There's no need for an Address function, because when you work with the object model, you have a Range object, and Range has an Address property that's readily available: a WorksheetFunction for it would be entirely redundant.
You know what worksheet the range is from (sheetname); you know what row you want (AT_rownum), and you know what column you want (1) - thus:
Dim AT_range As Range
Set AT_range = ThisWorkbook.Worksheets(sheetName).Cells(AT_rownum, 1)
AT_cellnum = AT_range.Address
But as Scott hinted at, a cell's address is rarely something you need in VBA: if you have a Range object, you likely already have everything you need.