Resolving runtime errors in Excel VBA

Question

I am taking my first plunge into Excel VBA and have a lot to learn.

With this macro, I'm setting up a file that will allow me to copy/paste a specific range of data from a weekly report based on the individual's name. Essentially, I want to be able to have the individual click a button and have the data get pulled automatically. Here's what I've put together so far:

Sub 1 (individual defines their name):

Sub Button1_Click()
Dim Name As Variant
Name = InputBox("Enter your name as it appears on the report", "Enter Name")
Worksheets("Summary").Range("A1").Value = Name
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub

Sub 2 (macro pulls data from report based on name entered in Sub 1):

Sub Report1_Click()
Dim Name As Variant
Set Name = Worksheets("Summary").Range("A1").Value
'check if Name is entered
If Name = "" Then
MsgBox ("Your name is not visible, please start from the Reference tab.")
Exit Sub
End If
Dim SourceFile As Workbook
Set SourceFile = Workbooks("filename.xlsm")
'check if source file is open
If SourceFile Is Nothing Then
'open source file
Set SourceFile = Workbooks.Open("C:\filename.xlsm")
SourceFile.Activate
Else
'make source file active
SourceFile.Activate
End If

Dim DestFile As Variant

Set DestFile = ActiveWorkbook.Worksheets("Input").Range("B2")

Dim ActiveCell As Variant

Set ActiveCell = Workbooks("filename.xlsm").Worksheets("Group").Range("A1")

Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveSheet.Range("$A$2:$DQ$11").AutoFilter Field:=1, Criteria1:=Name
Range("A7:CD7").Select
Selection.Copy
DestFile.Activate
ActiveSheet.Paste

Application.ScreenUpdating = True

ActiveWorkbook.Save

End Sub

I'm getting a runtime error 13 ("Type mismatch") on this line in Sub 2, and I have no idea why:

Set Name = Worksheets("Summary").Range("A1").Value

I have also gotten different errors at different points (including 91, 1004, and 9), which may or may not show up again if this one gets fixed.

UPDATE I resolved the error 13 on the previous line by removing the Set (at least I think I did), but now I'm getting an error 91 on:

Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate

Again, I'm brand new, so any assistance would be greatly appreciated.

Edit I'm using Excel 2011 for Mac, if that matters.

No need to put name onto worksheet. The variable can hold it just fine. — findwindow
Don't I have to set the variable to ensure that it pulls the value from the first subroutine? I might be mis-reading your comment (chalk it up to inexperience). — WhalesLarry
You can't "Set" a variable like that to the value of an object. In short, "Set"ing something takes an empty object variable and aligns it with a pre-existing object. So you could Dim xyz as Range and Set xyz = Range("A1"), but you cant dim xyz as String and Set xyz = Range("A1").Text — Grade 'Eh' Bacon
Also you shouldn't use the same variable in multiple subs like that. If you want both subs to refer to the same variable, you need to go outside of the subs in the main code module and dim it as a Public variable there. — Grade 'Eh' Bacon
Can two separate variables refer to the same value? Like if I had Sub 1's variable labeled X and Sub 2's variable labeled Y, but they both pulled from the same place, would that work? — WhalesLarry

Leonardo Atalla Leonardo Atalla · Accepted Answer · 2015-08-30T00:31:30

Because you're activating a non existing object.

It's better to check if the Cells.Find() returns you a Range (or Nothing instead) before trying activate it.

'Create a temporary Range
Dim temp_range As Range

'Notice that i removed the activation of the range at the end
Set temp_range = Cells.Find(What:=Name, After:=activecell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

'before anything, test if it's nothing...
If Not temp_range Is Nothing Then
    ' if not the you can activate..
     temp_range.Activate
    ' and work on the result...
    Debug.Print temp_range.Value , temp_range.Address
End If

Resolving runtime errors in Excel VBA

1 Answers