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

I don't see how A1:F2 could be cleared by your code. I do see how B3:F1 (or
B1:F3) could be if there was nothing in column F.

If you always know that column B has data in it, maybe you could change to
something like:

With Sheets("deelnemers")
.Range("B3:F" & .Range("B" & .Rows.Count).End(xlUp).row).ClearContents
End With

I think it's a good idea to explicitly give the .find command the parms you want
to use. You never know when they'll be something else--that turns out to be
completely wrong for your code.

Option Explicit
Sub CopyDBToData(CoName As String)
Dim dbColA As Range
Dim FirstdbCell As Range
Dim LastdbCell As Range

' clear any existing employees
With Sheets("deelnemers")
.Range("B3:f" & .Range("b" & Rows.Count).End(xlUp).Row).ClearContents
End With
' code to get the employees from the database
With Sheets("dbasedlnrs")
Set dbColA = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
With dbColA
Set FirstdbCell = .Cells.Find(What:=CoName, _
LookAt:=xlWhole, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
searchorder:=xlByRows, _
searchdirection:=xlNext)


Set LastdbCell = .Cells.Find(What:=CoName, _
LookAt:=xlWhole, _
after:=.Cells(1), _
LookIn:=xlFormulas, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious)
End With

If FirstdbCell Is Nothing _
Or LastdbCell Is Nothing Then
MsgBox "Not found"
Else
.Range(FirstdbCell, LastdbCell).Offset(0, 1).Resize(, 3).Copy _
Destination:=Worksheets("deelnemers").Range("B3")
Worksheets("staffelberekening").Range("J3") = CoName
End If

End With

End Sub

And the .offset/.resize question is kind of vague.

Maybe you want something like:

.Range(FirstdbCell.offset(1,0), LastdbCell.offset(-1,0)) _
.Resize(, 3).Copy _
Destination:=Worksheets("deelnemers").Range("B3")


To avoid the cells with the names themselves???

You may even want to add some checks to see if the firstdbcell and lastdbcell
are not the same:

if firstdbcell.row = lastdbcell.row then
'only one instance of CoName found, what should happen
else
'do the copy
end if




Pierre wrote:

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

--

Dave Peterson