VBA - Finding a repeating number through a long data series

sweather

New Member
Joined
Nov 19, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 19, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top