I use the following code to open an excel file and then
paste a summarised extract from one sheet (data) into
another (amp) on a daily basis. The place it pastes the
extract is dependent on the date of the file extract and
I use the Cells Find to locate the date within the sheet
where I want to paste the data.

'get summary data
Sheets("summary").Select
Range("D32:D43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("amp").Select
'find the cell with the date as header
Cells.Find(What:=ActiveCell.Value, After:=ActiveCell,
LookIn:=xlValues,
LookAt:=xlPart,SearchOrder:=xlByRows,
SearchDirection:=xlNext,MatchCase:=False).Activate
Selection.Offset(1, 0).Select
'move down one
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'paste data

Until recently the routine has worked fine, but now trips
up at 'Cells Find' with the error message "Runtime error
91 - Object variable or Block variable not set".

It was working fine up to 10/09, but falls over with
10/10 and subsequent day's data. Is there a constraint
with date formats that I've missed or can anybody suggest
how I should amend my code to fix this?

Any help gratefully received.

Thanks Simon

Sub test()
Dim rFound As Range

' first look for the value
Set rFound = _
Sheets("amp").Cells.Find(What:=ActiveCell.Value)

If Not (rFound Is Nothing) Then
' found ! so copy/paste
Sheets("summary").Range("D32:D43").Copy
rFound.Offset(1, 0).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End If


End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
I use the following code to open an excel file and then
paste a summarised extract from one sheet (data) into
another (amp) on a daily basis. The place it pastes the
extract is dependent on the date of the file extract and
I use the Cells Find to locate the date within the sheet
where I want to paste the data.

'get summary data
Sheets("summary").Select
Range("D32:D43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("amp").Select
'find the cell with the date as header
Cells.Find(What:=ActiveCell.Value, After:=ActiveCell,
LookIn:=xlValues,
LookAt:=xlPart,SearchOrder:=xlByRows,
SearchDirection:=xlNext,MatchCase:=False).Activat e
Selection.Offset(1, 0).Select
'move down one
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'paste data

Until recently the routine has worked fine, but now
trips
up at 'Cells Find' with the error message "Runtime error
91 - Object variable or Block variable not set".

It was working fine up to 10/09, but falls over with
10/10 and subsequent day's data. Is there a constraint
with date formats that I've missed or can anybody
suggest
how I should amend my code to fix this?

Any help gratefully received.

Thanks Simon


.

Patrick

Many thanks for your quick response. I have tried what
you have suggested but can't get this to work. I'm afraid
I'm still a bit new to VBA code.

My complete code was as follows;

Sheets("data").Select
Range("A2:C500").Select
Selection.Clear
Range("A7").Select
ChDir "C:\documents and settings\my documents\my
files\data"
Workbooks.Open Filename:="C:\documents and settings\my
documents\my files\data\amp.xls"
Range("A1:C500").Select
Selection.Copy
Windows("volume.xls").Activate
Range("A2").Select
ActiveSheet.Paste
'new code to get date
Sheets("data").Select
Range("B2").Select
Selection.Copy
Sheets("anz").Select
Range("B1").Select
ActiveSheet.Paste
'get summary data
Sheets("summary").Select
Range("D32:D43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("anz").Select
'find the cell with the date as header
Cells.Find(What:=ActiveCell.Value, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Offset(1, 0).Select
'move down one
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Having copied the header date of the relevant data
extract from "B1" of sheet "data" to "B2" of sheet "amp",
I am using Cells.Find to locate that same date within the
same sheet "amp" and then paste the data extract
(d32..d43) one cell below this point.

Given this code was working OK I'm still wondering why it
should suddenly give problems. Is there any size
constraint that would make it too difficult for the
Cells.Find to locate the date? The file I am using has
built in size since the beginning of the year to 1.8mb
and each of the 25 sheets are now 52 columns wide - so I
guess it has to check a lot of cells before finding the
right one - could this be my problem?

Inserting your code would make it as follows:

Sheets("data").Select
Range("A2:C500").Select
Selection.Clear
Range("A7").Select
ChDir "C:\documents and settings\my documents\my
files\data"
Workbooks.Open Filename:="C:\documents and settings\my
documents\my files\data\amp.xls"
Range("A1:C500").Select
Selection.Copy
Windows("volume.xls").Activate
Range("A2").Select
ActiveSheet.Paste
'new code to get date
Sheets("data").Select
Range("B2").Select
Selection.Copy
Sheets("anz").Select
Range("B1").Select
ActiveSheet.Paste

Sub test ()
Dim rFound As Range
'first look for the value
Set rFound = _
Sheets("amp").Cells.Find(What:=ActiveCell.Value)
If Not (rFound is Nothing) Then
'found! so copy/paste
Sheets("summary").Range("D32:D43").Copy
rFound.Offset(1, 0).PasteSpecial Paste:=xlValues
Application.CutCopyMode=False
End If
End Sub

I must be missing some syntax as I can't get this to
work. What am I missing please?

Thanks, Simon

Patrick

I've noticed that when I change the date format of the header date to
a number and similarly change the date format in the sheet that the
'Cells.Find (What:=ActiveCell.Value) is trying to locate, then my
original code works fine. So it seems that for some reason it doesn't
like it when the value is formatted as a date.

So this appears to be a formatting problem. Can you suggest any
alternative code that would solve this.

Thanks, Simon