Excel Formula Incrementing By One Randomly

[latticeman]

Hello,

I have been attempting to make a spreadsheet for my fantasy football league draft. In the spreadsheet, I have a searchable dropdown list which I constructed using this tutorial: How to create a searchable drop down list in Excel?.

My data is taken from a dynamic web query, and my dropdown list is for the names of all of the football players. However, I run into a problem when pasting the three columns required. My formula seems to work for the first 197 lines. However, once I get down to line 198, one of the counters increments by an additional number. All I am doing is copying and pasting the single cell into the whole range, so I am not sure what the problem could be. Here is the formula I start off with in cell N2:

=--ISNUMBER(IFERROR(SEARCH($I$2,Rankings!$B2),""))

Thus, once I get down to cell N198, I would expect it to be

=--ISNUMBER(IFERROR(SEARCH($I$2,Rankings!$B199),""))

However, for some reason, even though the line above is correct with B198 as the last address, the formula for N198 is depicted as

=--ISNUMBER(IFERROR(SEARCH($I$2,Rankings!$B200),""))

As can be seen, an additional 1 is added to the final number for no apparent reason, and this continues through the remaining three lines. Is there any reason for this error, and anything I could do to fix it?

If there is any additional clarification needed, I would be happy to provide it. Thanks!

 

[Jonmo1]

Works as expected for me.

But I get an error about invalid columns or something when it's doing the ActiveSheet.RefreshAll action.
It seems to be querying some data, but I don't have access to that data.

 

[latticeman]

That's very strange; I have no problems querying the data, haven't gotten any errors when attempting to do so. Still running into the same problems, however, and I'm unsure why the problem always occurs at that specific spot.

 

[latticeman]

Ran into another interesting problem. When I go into debugging mode and do it one line at a time, the whole thing seems to work. However, when I run the whole thing at once, the addresses actually change; for example, part of P1 changes from $B$201 to $B$202. What would result in the absolute addresses changing?

 

[Jonmo1]

The problem is all the queries to google docs.
Those queries don't run instantly, and the 'reset' macro proceeds to rewrite those formulas before the queries are done..
With those formulas referring to data from those queries (that have not yet been updated), When those queries get updated, rows are deleted/inserted during it's refresh.
Throwing those formulas out of sync.

The solution I've found is to go to the properties of each query and uncheck 'enable background refresh'
Go to the Data Tab, click 'Queries and connections'. Then you can right click each query and uncheck the background refresh option.
This forces the macro to wait until the queries are completed before continuing it's work.