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!

 

[RoryA]

Welcome to the forum.

I have to say that I have never seen that happen. Is there any chance you could post a file somewhere that I can take a look at? (I assume you have no hidden rows)

 

[Peter_SSs]

Not answering your question, but if you start with $B2 in cell N2
why would you expect $B199 in cell N198?

Does it actually mean that you have an unexpected increment of 2, not 1?

 

[latticeman]

Welcome to the forum.

I have to say that I have never seen that happen. Is there any chance you could post a file somewhere that I can take a look at? (I assume you have no hidden rows)

I've narrowed down the error to one of my drafting functions. If I click the "auto draft" buttons there, then the indexing error moves up the list each time corresponding to the number of times I click it. I'm not sure what could cause that error, but I'm pretty sure that's where it originated. I've uploaded the file to this website; I'm not sure if there's any other protocol for posting files, but this is the best option I could think of in the moment.
Fantasy Football 2017 - Copy.xlsm :: Free File Hosting - File Dropper: File Host for Mp3, Videos, Music, Documents.

Not answering your question, but if you start with $B2 in cell N2
why would you expect $B199 in cell N198?

Does it actually mean that you have an unexpected increment of 2, not 1?

Oh yes, I meant that I started in cell N1, not N2; unfortunately it looks like I cannot edit the post though.

 

[Marcelo Branco]

Maybe a silly hypothesis. Check if row 199 is hidden. Just in case...

M.

 

[Jonmo1]

So it's actually a VBA code/macro that is doing the work. It's not you doing it by hand.

Can you post the actual code that is doing the work (we probably don't need to see the actual book, just the code)
it's probably inserting/deleting rows in the middle.

 

[Marcelo Branco]

I wanted to say: check if row 198 is hidden

M.

 

[latticeman]

I wanted to say: check if row 198 is hidden

M.

Looks like all of my rows are unhidden, so that shouldn't be an issue.

So it's actually a VBA code/macro that is doing the work. It's not you doing it by hand.

Can you post the actual code that is doing the work (we probably don't need to see the actual book, just the code)
it's probably inserting/deleting rows in the middle.

The problem arises both with pasting manually and using a macro. Here is my code; the relevant issue involves the pasting and deleting on the "Draft Picks" sheet. Apologies if the code is ambiguous, I messed up some of the aesthetics just in an attempt to get functionality going.

Sub Draft_Player(ByVal Name As String)
    Dim Names As Range
    Dim r As Integer
    Dim c As Integer
    Dim entire As Range
    Dim last1 As Integer
    Dim last2 As Integer
    Dim pos As String
    Dim Team As String
    Dim col As Range
    Dim loc As Range
    r = Sheets("Draft Picks").Cells(Sheets("Draft Picks").Rows.Count, "C").End(xlUp).Row + 1
    Team = Sheets("Draft Picks").Range("B" & r)
    Sheets("Draft Picks").Range("C" & r).Value = Name
    ' Delete from rankings
    ThisWorkbook.Worksheets("Rankings").Activate
    Set Names = ActiveSheet.Range("B:B")
    r = Names.Find(Name).Row
    pos = ActiveSheet.Range("D" & r).Value
    Set entire = Names.Find(Name).EntireRow
    ActiveSheet.Range("A" & r & ":K200").Value = ActiveSheet.Range("A" & r + 1 & ":K201").Value
    last1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Range(last1 + 1 & ":" & last1 + 1) = entire
    ThisWorkbook.Worksheets("Draft Picks").Activate
    last2 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "N").End(xlUp).Row
    ActiveSheet.Range("N" & last2 & ":P" & last2).Delete
    ThisWorkbook.Worksheets("Rankings").Activate
    ActiveSheet.Rows(last1).Delete
End Sub

 

[Jonmo1]

I just downloaded your book, and I'm unable to replicate the problem.

Can you describe exactly step by step the actions you take that result in this offset row # ?

 

[latticeman]

I just downloaded your book, and I'm unable to replicate the problem.

Can you describe exactly step by step the actions you take that result in this offset row # ?

I start off and hit the "DRAFT" button located in cell H2 10 times or so, then hit the "RESET" button. After that runs, there should be 200 rows in columns N, O, and P. However, once I scroll down to the 200th, that one is wrong. In that case, looking between cells N189 and N190 there is an unexplained increase of 2 in the final address, which results in an error in the final line.