So I have the below macro, which takes 3 pieces of data to 1.) open the correct file with the date and 2.) get the value at the row and column number based on the data entered into 'OU" and "Acct". The result is the answer variable. This is working. But what I would like to do is put this into a function and be able to call it from a worksheet, passing in the three values. When I try this, it won't open the workbook. When it gets to that step, it just returns #VALUE!.
Any idea how I can make this work as a function?
' Macro1 Macro
Dim ou As String ' Parameter 1
Dim acct As String ' Parameter 2
Dim mydate As String ' Parameter 3
Dim fn As String
Dim row As Double
Dim column As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim OURange As Range
Dim AcctRange As Range
row = 0
column = 0
ou = "b"
acct = "1"
mydate = 20171215
fn = "Q:\Desktop Files 2015-03-31\test1" & mydate & ".xlsx"
Set wb = Workbooks.Open(fn) 'name of the with the info
Set ws = wb.Sheets("Sheet1") 'name of the worksheet with the info
Set OURange = ws.Range("D:D") 'column of the worksheet where the OU is
Set AcctRange = ws.Range("a1:zz1") 'row of the worksheet where the acct is
row = Application.WorksheetFunction.Match(ou, OURange, 0)
For Each c In AcctRange
If c.Value2 = acct Then
column = c.column
If (column > 0) Then
answer = ws.Cells(row, column).Value2