VBA find sporadic error

[MrSak87]

Hi guys

I have a section of code which looks through a section of data, finds it and copies it somewhere else. Essentially I have milliseconds running down the A column from 0 to 40000 - I want to pluck out numbers from what I type in a box in the Ca column.

Dim x As Range, y As Range
Set x = Columns(1).Find(Cells(3, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Set y = Columns(1).Find(Cells(4, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Range(Cells(x.Row, 1), Cells(y.Row, 1)).Resize(, 7).Copy

The codes only works under certain conditions. i.e if I type in 0.1 seconds to 0.9 or 5.1 to 5.9

However it doesn't like it if it crosses up into the next second i.e 0.6 to - 2 seconds. And it especially doesn't like it if the values I'm plucking out are above 10 seconds. Some times I can fix this by reversing the values i.e 10.9 to 10.1 but I still get errors.

Is my code wrong? is there a more robust version?

Thank you for your time

 

[CharlesChuckieCharles]

Unless Column A is strickly sorted and your input cells are ascending in Column CA

There is a chance that Row X may be > Row Y ie Range(Cells(x.Row, 1), Cells(y.Row, 1)) might end up like Range(Cells(23,1),Cells(9,1))

 

[MrSak87]

How do you mean?

instead of...

Dim x As Range, y As Range
Set x = Columns(1).Find(Cells(3, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Set y = Columns(1).Find(Cells(4, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Range(Cells(x.Row, 1), Cells(y.Row, 1)).Resize(, 7).Copy

have

Dim x As Range, y As Range
Set x = Columns(1).Find(Cells(4, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Set y = Columns(1).Find(Cells(3, "ca").Value, LookIn:=xlValues, LookAt:=xlPart)
Range(Cells(x.Row, 1), Cells(y.Row, 1)).Resize(, 7).Copy

?