VBA Worksheet Change "Invalid Procedure" issue

[blopez5518]

Hello,

I am new to vba and I started working on a worksheet change, below is my current code. When I tested it I used "ActiveCell.Offset(-1, 2) = 1" and everything worked great. Well now I am placing my code to retrieve data from another workbook and keep getting an invalid procedure call or argument error with the lines of code in red. The highlighted code works as a macro just fine. This exact code also works fine when getting the data from same sheet and/or another sheet within the same workbook. So it seems that there is an issue referencing the other workbook. I'm not sure what I'm doing wrong or if this code can even do what I am wanting. Any help is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("Funded.xlsx")
Set ws = wb.Worksheets("Funded")


If Target.Count > 1 Then
Else
If Target.Value = "" Then
Else
If Target.Column = 2 Then
Dim response As String
response = Application.InputBox("Deal#:")
If response = False Then
Value = ""
Else
ActiveCell.Offset(-1, 1).Value = response
End If
End If
End If
End If


If Target.Count > 1 Then
Else
If Target.Value = "" Then
Else
If Target.Column = 3 And ActiveCell.Column = 3 Then
ActiveCell.Offset(-1, 1) = ws.Range("I:I") _
.Cells(Application.Match(ActiveCell.Offset(-1, 0), _
ws.Range("A:A"), 0))
Else


If Target.Count > 1 Then
Else
If Target.Value = "" Then
Else
If Target.Column = 3 Then
ActiveCell.Offset(-1, 2) = ws.Range("I:I") _
.Cells(Application.Match(ActiveCell.Offset(-1, 1), _
ws.Range("A:A"), 0))


End If
End If
End If
End If
End If
End If
End Sub

 

[RoryA]

The most likely cause is that the match is failing and so passing an error value to the Cells property. You should test the result of Match first.

 

[blopez5518]

I feel special, it was formatting error with what was being matched. Lmao

It works now, however now that it is working I'm realizing that the referenced workbook has to be open for it to work, it also has been during testing so I didn't know until I tried to close the workbook.

Is there a way to reference the closed workbook without it physically being opened?

 

[RoryA]

Only by putting a formula into the cells.