AlexMason
11-03-2009, 04:38 AM
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?
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?