Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
database in excel problem
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delicate Excel-database problem | Excel Discussion (Misc queries) | |||
problem vba dialoguebox and database in excel | Excel Programming | |||
Problem with the import of a database table into Excel 2003. | Excel Programming | |||
Excel function to search and access database problem | Excel Programming | |||
MS Access Database Connection problem in Excel XP 2002 | Excel Programming |