Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Where do I find A bell curve I can copy and paste | Charts and Charting in Excel | |||
Macro to find copy "header" and paste | Excel Discussion (Misc queries) | |||
Macro to find, copy, and paste until value change | Excel Worksheet Functions | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions |