can someone help me. i'm trying to set a string, and then show it in a msgbox using vba in excel and for some reason i can't get it working.
says object doesn't support this property or method. highlighting "Fname = Report.Range("J11").Text".
if i comment this out, I get the same error message on "Msgbox Report"
can't figure out what's up
Dim Wb As Workbook ' current workbook
Dim Report As Workbook ' referenced workbook
Dim Ffn As String ' full file name of Report
Dim Fname As String ' retrieved file name
Ffn = "H:\F&O Report Instructions Macro 1-22-2014.xlsm"
Set Report = GetWorkbook(Ffn)
If Report Is Nothing Then
MsgBox "Couldn't find the Report", vbCritical, _
Fname = Report.Range("J11").Text
'If Len(Fn) Then
Private Function GetWorkbook(Wn As String) As Workbook
Dim Wb As Workbook
Dim Sp() As String
Sp = Split(Wn, "\")
On Error Resume Next
Set GetWorkbook = Workbooks(Sp(UBound(Sp)))
If Err Then
Set GetWorkbook = Workbooks.Open(Wn)