Activate Method of Range Fails

[AlexMason]

with this code

Sub Consolidate2TESTONLY()

Dim CContent
Dim MYDATA As DataObject
Set MYDATA = New DataObject
Dim MasterLgth, MergeLgth As Integer
Dim MergeChkRow, MasterListRow As Integer
Dim CELL, CELL2, Content As String
Dim firstAddress2


MasterLgth = FindLastRow1(16)
MergeLgth = FindLastRow5(16)

For MergeChkRow = 2 To MergeLgth


If Sheets("Master List").Cells(MergeChkRow, 16) <> "" Then

CELL = "P" & MergeChkRow
Sheets("Merge 2 Master").Range(CELL).Copy 'select the defined cell
MYDATA.GetFromClipboard 'get the data off the clipboard
CContent = MYDATA.GetText(1) 'assign the text from the Cell to a variable
'MsgBox ">" & Content & "<"
CContent = Trim(Replace(CContent, Chr(13) & Chr(10), ""))

Set rngFind = Sheets("Master List").Cells.Find(What:=CContent, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFind Is Nothing Then

firstAddress2 = rngFind.Address
rngFind.Activate

End If

CELL2 = ActiveCell.Address
CELL = MergeChkRow & ":" & MergeChkRow
Sheets("Merge 2 Master").Range(CELL).Copy
Sheets("Master List").Range(CELL).PasteSpecial Paste:=xlPasteAll
Sheets("Merge 2 Master").Range(CELL).Delete

End If

Next MergeChkRow


End Sub


i get this error


Run-time error '1004': Activate method of Range class failed



and this happens at the line : rngFind.Activate


now this worked when i ran through it once, but on the second loop it broke down and now doesnt work at all. what am i doing wrong?

[lucas]

Can't test it because I don't have your dataobject.

I'm guessing you're not using Option Explicit at the top of your code module or you would know that you have not dimensioned the variable rngFind.

Does the sheet "master list" exist?

[mdmackillop]

It is not clear which sheet is active when you run the code. If it is not Master List, the code as written will cause problems.

Set rngFind = Sheets("Master List").Cells.Find(What:=CContent, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

This can be run from any sheet, but the ActiveCell address will refer to the active sheet, not necessarily the one you want on Master List.

You cannot activate a range other than on the active sheet. Try
Application.goto rngFind