Hi experts,

I have 2 sheets:

1. Sheet("deelnemers") for the data used 'deelnemers means employees
the data is stored in "B3:F992", depending on the number of employees,
so 5 items per employee
2. Sheet("dbasedlnrs") to store the data from sheet("deelnemers in")
the data is stored in range("A:F") where a is the company name

there is a company name to recognise the employees from. This company name is
on
sheet("staffelberekening").range("J3")

To retrieve the data i use a user inputform where the user can input the
companyname (CoName)
i use the following code to then retrieve the data

Sub CopyDBToData(CoName As String)
Sheets("deelnemers").Select
' clear any existing employees
With Sheets("deelnemers")
..Range("B3", .Range("F" & Rows.Count).End(xlUp)).ClearContents
End With
' code to get the employees from the database
With Sheets("dbasedlnrs")
Set dbColA = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set FirstdbCell = dbColA.Find(What:=CoName, LookAt:=xlWhole)
Set LastdbCell = dbColA.Find(What:=CoName, LookAt:=xlWhole,
SearchDirection:=xlPrevious, searchOrder:=xlByColumns)
.Range(FirstdbCell, LastdbCell).Offset(0, 1).Resize(, 3).Copy Sheets
("deelnemers").Range("B3")
Sheets("staffelberekening").Range("J3") = CoName
End With
End Sub

When i run the code the only error message i get is on the line
..Range(firstdbCell.....
Furthermore the range A1:F2 is cleared from the 'deelnemers' sheet and that
is not what i want...

I think the Offset or resize statement is incorrect but i cannot seem to
solve it

Can anyone help me get the right code please?
thanks,
Pierre

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1