VBA - Finding a repeating number through a long data series

[sweather]

Hi All,

I am drafting my first find code to identify a series of repeating numbers in a data series. For each number found in the data series, i am copying both the number and the data corresponding to when the date occurs.

My code is below, but my challenges that i would like your assistance on are:

1. a "run-time error '91: object variable or With block variable not set".
This arises due to the line of code: rownumber = BDeg.Row 'this code outputs the rownumber of where the data is found

2. The code is currently picking up exact references. For example if I am searching for 260.61, the find is only identifying an exact match of 260.61 in the data series. Is there a way to identify a say a range of 259.00 - 261.00 ?

3. The "SearchDirection:=xlNext" is useful and searches my data series going forward. I have played around a little with "xlPrevious", however is there another way to guide the search to go backward in time (by date) ?

Thanks in advance !

Sub FindTest()

Dim OriginXColumn As Range
Dim BDeg As Range
Dim OriginX As Double
Dim rownumber As Long
Dim X As Integer

X = 11

Do

OriginX = Worksheets("Master").Cells(X, 3)

Set BDeg = Worksheets("Master").Columns("E:E").Find(What:=OriginX, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
rownumber = BDeg.Row 'this code outputs the rownumber of where the data is found

Worksheets("Master").Cells(X, 9).Value = Worksheets("Master").Cells(rownumber, 5) ' this copies the found number to the row
Worksheets("Master").Cells(X, 8).Value = Worksheets("Master").Cells(rownumber, 1)

X = X + 1

Loop Until IsEmpty(Cells(X, 3))

End Sub

 

[StephenCrump]

Welcome to the Forum!

I suggest that rather than looping though the data, you use filter to find all the values required (you can specify lower and upper bounds) and copy the results in bulk.
You could then sort the results by date, if required.

The macro recorder will get you started with the code.

 

[sweather]

Thanks StephenCrump !

I have played around this morning with your suggestion, though unfortunately because i am seeking to identify results for each row (ie corresponding matches through time), the approach to filter and copy and paste results in bulk, make it very difficult, because you need to do the filtering/copying and pasting for each row.....

Would you have any other suggestions ?

I have also been playing around with the Application.Match function, though i still have the same "tolerance" point on the matches....

 

[StephenCrump]

Can you please post some sample data, indicating the filtered results you'd like to see copied?

It's best to use the XL2BB add-in to do this - that way we can copy your layout and formulae without needing to retype:

XL2BB - Excel Range to BBCode