Macro Button - Compile Error Variable Not Defined

[chrono2483]

Hello,

I have a spreadsheet, where I'm trying to use a Button to run a macro. There are two tabs in this workbook: Sheet 1 (where the raw data is) and Sheet 2 (where I want to pull data from Sheet 1 to display).

What I want the button to do is reference Sheet 1, and search Column C for all occurrences on the word "Name", copy it and Paste it in Column AA on the same Sheet 1.

The below code is what I'm using (taken from a different tool, where it works), but I get the error "Compile Error Variable Not Defined", and highlights the "Set SrchRng", just below 'Sheets("Sheet1").Select'. Would anyone know what I could be doing wrong?

Sub Button1_Click()
'
' Button1_Click Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'


Dim i As Long

 Sheets("Sheet1").Select
 Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("C25000").End(xlUp))
 Set myCell = SrchRng.Find("Name", LookIn:=xlValues)
 If Not myCell Is Nothing Then
 firstAddress = myCell.Address
 i = 3
 myCell.Copy Cells(i, "AA")
 Else
 MsgBox "Can't find search string"
 Exit Sub
 End If
 Do
 Set myCell = SrchRng.FindNext(myCell)
 If myCell Is Nothing Then Exit Do
 If myCell.Address = firstAddress Then Exit Do
 i = i + 1
 myCell.Copy Cells(i, "AA")
 Loop


End Sub

Thanks!

 

[Trebor76]

Hi there,

Though you don't show it, your code must have Option Explicit at the very start (top of the module). This is good practice as it forces you declare all your variables at run time. If you don't declare all your variables Excel will declare them as variants which are the most expensive type of variable in terms of memory.

You actually need two variables declared which would go immediately beneath your existing variable:

Dim SrchRng As Range
Dim myCell As Range

HTH

Robert