Find, copy and paste
 

Hello all,
Tried these two codes, both give me trouble (*****)
at different intervals.
The first one works in another workbook ( this one I'm working on is a copy
of it, but edited somewhat, the sheet names are still the same, just the
layout is slightly different)
Any help would bw appreciated.
P.S. posted this question about 4 hours ago, but can't find my post, so
sorry if it show's up twice...

Sub JKsend3()
Dim myFind As Integer
Dim rng As Range
*****myFind = Worksheets("INPUT").Range("$E$36").Value
Set rng = Worksheets("INPUT").Range( _
"C493:C979").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)


If Not rng Is Nothing Then

Worksheets("INPUT").Range("D488:BA488").Copy
***** rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Sub send9()
Dim myFind As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Set wks = ActiveSheet
Set rngToSearch = Worksheets("INPUT").Range("C493:C979")
Set rngFound = rngToSearch.Find(What:=wks.Range("$E$36"), _
LookAt:=xlPart, MatchCase:=False)

If Not rngFound Is Nothing Then
Worksheets("INPUT").Range("D488:BA488").Copy
rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Find, copy and paste
 

What is in
Worksheets("INPUT").Range("E36").Value
and is it an integer as opposed to a long or a double...
--
HTH...

Jim Thomlinson


"ufo_pilot" wrote:

Hello all,
Tried these two codes, both give me trouble (*****)
at different intervals.
The first one works in another workbook ( this one I'm working on is a copy
of it, but edited somewhat, the sheet names are still the same, just the
layout is slightly different)
Any help would bw appreciated.
P.S. posted this question about 4 hours ago, but can't find my post, so
sorry if it show's up twice...

Sub JKsend3()
Dim myFind As Integer
Dim rng As Range
*****myFind = Worksheets("INPUT").Range("$E$36").Value
Set rng = Worksheets("INPUT").Range( _
"C493:C979").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)


If Not rng Is Nothing Then

Worksheets("INPUT").Range("D488:BA488").Copy
***** rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Sub send9()
Dim myFind As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Set wks = ActiveSheet
Set rngToSearch = Worksheets("INPUT").Range("C493:C979")
Set rngFound = rngToSearch.Find(What:=wks.Range("$E$36"), _
LookAt:=xlPart, MatchCase:=False)

If Not rngFound Is Nothing Then
Worksheets("INPUT").Range("D488:BA488").Copy
rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Find, copy and paste
 

Thanks for responding, Jim
In E36, is the date, in the old version, it was a julian date(1-365), but
since the new workbook is covering 2006 and 2007, I stayed away from julian
dates and used dates as in 1-Sep-06 and also formated it as text (38969),
since that number is unique...
???


"Jim Thomlinson" wrote:

What is in
Worksheets("INPUT").Range("E36").Value
and is it an integer as opposed to a long or a double...
--
HTH...

Jim Thomlinson


"ufo_pilot" wrote:

Hello all,
Tried these two codes, both give me trouble (*****)
at different intervals.
The first one works in another workbook ( this one I'm working on is a copy
of it, but edited somewhat, the sheet names are still the same, just the
layout is slightly different)
Any help would bw appreciated.
P.S. posted this question about 4 hours ago, but can't find my post, so
sorry if it show's up twice...

Sub JKsend3()
Dim myFind As Integer
Dim rng As Range
*****myFind = Worksheets("INPUT").Range("$E$36").Value
Set rng = Worksheets("INPUT").Range( _
"C493:C979").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)


If Not rng Is Nothing Then

Worksheets("INPUT").Range("D488:BA488").Copy
***** rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Sub send9()
Dim myFind As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Set wks = ActiveSheet
Set rngToSearch = Worksheets("INPUT").Range("C493:C979")
Set rngFound = rngToSearch.Find(What:=wks.Range("$E$36"), _
LookAt:=xlPart, MatchCase:=False)

If Not rngFound Is Nothing Then
Worksheets("INPUT").Range("D488:BA488").Copy
rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Find, copy and paste
 

OK, New Day... jumpstarted it with a fresh brainwave...
changed rng to rngFound, and of course now it works fine

If Not rngFound Is Nothing Then
Worksheets("INPUT").Range("D488:BA488").Copy
***rngFound.Offset(0, 1).PasteSpecial xlValues
I still appreciate you responding, Jim

"Jim Thomlinson" wrote:

What is in
Worksheets("INPUT").Range("E36").Value
and is it an integer as opposed to a long or a double...
--
HTH...

Jim Thomlinson


"ufo_pilot" wrote:

Hello all,
Tried these two codes, both give me trouble (*****)
at different intervals.
The first one works in another workbook ( this one I'm working on is a copy
of it, but edited somewhat, the sheet names are still the same, just the
layout is slightly different)
Any help would bw appreciated.
P.S. posted this question about 4 hours ago, but can't find my post, so
sorry if it show's up twice...

Sub JKsend3()
Dim myFind As Integer
Dim rng As Range
*****myFind = Worksheets("INPUT").Range("$E$36").Value
Set rng = Worksheets("INPUT").Range( _
"C493:C979").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)


If Not rng Is Nothing Then

Worksheets("INPUT").Range("D488:BA488").Copy
***** rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub

Sub send9()
Dim myFind As Integer
Dim rngToSearch As Range
Dim rngFound As Range
Set wks = ActiveSheet
Set rngToSearch = Worksheets("INPUT").Range("C493:C979")
Set rngFound = rngToSearch.Find(What:=wks.Range("$E$36"), _
LookAt:=xlPart, MatchCase:=False)

If Not rngFound Is Nothing Then
Worksheets("INPUT").Range("D488:BA488").Copy
rng.Offset(0, 1).PasteSpecial xlValues

Else
MsgBox myFind & " was not found"
End If
Sheets("PNT032").Select
Range("J46").Select
End Sub