So the first time I run the code everything works fine the second time I run it i get a 1004 and it says application defined for object defined error
Please help very new
Bolded Words where range starts is where the problem is
Code:
Sub Macro3()'
' Macro3 Macro
'Test Trial for Plugging in cell location
'Sub TestAccount()
'
' TestAccount Macro
'
'Sub Find_Accounts()
Dim Found As Range
Dim sMessage As String
Dim s As String
'
Set Found = Columns("A").Find(What:="Kevin", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Found Is Nothing Then
sMessage = "No Match Found for ""Kevin""" & vbCr
Else
sMessage = """Kevin"" found in cell " & Found.Address & " Row: " & Found.Row & vbCr
s = Found.Row
End If
MsgBox sMessage
MsgBox s
'
Set currentSheet = ActiveSheet
Set sourceSheet = Sheets("Sheet1")
Set destinationSheet = Sheets("Sheet2")
sourceFirstRow = 32
sourceLastRow = s
sourceFirstColumn = 1
sourceLastColumn = 100
destinationRow = 7
destinationColumn = 3
sourceSheet.Select
Range(Cells(sourceFirstRow, sourceFirstColumn), _
Cells(sourceLastRow, sourceLastColumn)).Copy
destinationSheet.Select
Cells(destinationRow, destinationColumn).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells(1, 1).Select
currentSheet.Activate
Option Explicit
Sub Find_Accounts()
Dim oFound As Object
Dim sMessage As String
Dim sAddress As String
Dim d As String
'
Set oFound = Columns("AB").Find(What:="Orkis", After:=[AB1], LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If oFound Is Nothing Then
sMessage = "No Match Found for ""Orkis""" & vbCr
Else
sAddress = oFound.Address(False, False)
sMessage = """Orkis"" found in cell " & sAddress & " Row: " & oFound.Row & vbCr
With Range("AB2:AC" & oFound.Row)
.Value = .Value
End With
End If
MsgBox sMessage
Set oFound = Nothing
End Sub
Bookmarks