Thread: Seemingly erratic behavior when auto-filling cells

I couldn't think of a more suitable name for the problem.

In the workbook, there are two sheets. One is the source sheet where certain values are parsed and compiled into a table in the first sheet. The code is supposed to write a single dot ( "." ) to every empty cell between every value in the first sheet (the one with the table for values to be filled in from the other sheet). The problem is that some particular rows remain empty. I can not figure out why.

A simplified excel file with the working example is attached and below is the code:

Public r2 As Range
Sub GetWpFromXMLs()
Application.ScreenUpdating = False

    Dim sRange As Range
    Dim rStart As Range
    Dim rEnd As Range
    
    Dim TAGSTRING As String

    dRow = 1 '// Offset value for "destination" Row
    dCol = 3 '// Offset value for "destination" Column
    rtn = 0 '// for designating when the current XML entry is a non-firearm
    Set rStart = Sheets("WeaponsXML").Range("C39")
    Set rEnd = Sheets("WeaponsXML").Range("C64")

    Do
        If dRow = 1 Then
            TAGSTRING = "<uiIndex>"
        ElseIf dRow = 2 Then
            ActiveSheet.Range("A1").Offset(dRow - 1, dCol).Value = "£"
            TAGSTRING = "X"
        ElseIf dRow = 3 Then TAGSTRING = "<ubWeaponType>"
        ElseIf dRow = 5 Then TAGSTRING = "<usRange>"
        ElseIf dRow = 6 Then TAGSTRING = "<ubImpact>"
        ElseIf dRow = 7 Then TAGSTRING = "<ubReadyTime>"
        ElseIf dRow = 9 Then TAGSTRING = "<bBurstAP>"
        ElseIf dRow = 11 Then TAGSTRING = "<APsToReload>"
        ElseIf dRow = 12 Then TAGSTRING = "<APsToReloadManually>"
        ElseIf dRow = 13 Then TAGSTRING = "<ubBurstPenalty>"
        ElseIf dRow = 14 Then TAGSTRING = "<AutoPenalty>"
        ElseIf dRow = 15 Then TAGSTRING = "<ubShotsPerBurst>"
        ElseIf dRow = 16 Then TAGSTRING = "<bAutofireShotsPerFiveAP>"
        ElseIf dRow = 17 Then TAGSTRING = "<bAccuracy>"
        ElseIf dRow = 20 Then TAGSTRING = "<ubCalibre>"
        ElseIf dRow = 21 Then TAGSTRING = "<ubMagSize>"
        ElseIf dRow = 23 Then TAGSTRING = "<ubDeadliness>"
        ElseIf dRow = 27 Then TAGSTRING = "<ubAttackVolume>"
        ElseIf dRow = 34 Then TAGSTRING = "<ubWeaponClass>"
        ElseIf dRow = 43 Then TAGSTRING = "<ubShotsPer4Turns>"
        ElseIf dRow = 69 Then TAGSTRING = "<szWeaponName>"
        ElseIf dRow = 70 Then
            Set rStart = rEnd.Offset(1, 0)
            Set rEnd = Sheets("WeaponsXML").Range(rStart.Offset(0, -1), Sheets("WeaponsXML").Cells(65535, 2)).Find(What:="</WEAPON>", lookat:=xlPart).Offset(0, 1)
            dCol = dCol + 1
            dRow = 0
            TAGSTRING = "X"
        Else
            ActiveSheet.Range("A1").Offset(dRow, dCol).Value = "."
            TAGSTRING = "X"
        End If

        If TAGSTRING <> "X" Then
            Set sRange = Sheets("WeaponsXML").Range(rStart, rEnd).Find(What:=TAGSTRING, lookat:=xlPart)
            If Not sRange Is Nothing Then
                ActiveSheet.Range("A1").Offset(dRow - 1, dCol).Value = Mid(sRange, (InStr(1, sRange, ">") + 1), (Len(sRange) - (InStr(1, sRange, ">") * 2) - 1))
                TAGSTRING = "X"
            Else
                ActiveSheet.Range("A1").Offset(dRow - 1, dCol).Value = "."
            End If
        End If
        dRow = dRow + 1

    Loop Until dCol = 15

Application.ScreenUpdating = True
End Sub

In the excel file, press the button with ">>" text in "A3" to see it work.

The rows that remain empty are 4, 8, 10, 18, 22, 24, 28, 35, 44.

Any help is much appreciated.

Those appear to be rows that you are not specifically picking up in the code.

One of the headings is "Reliability" and there dowsn't appear to be any meta data for that heading. Could that be the case for the other rows?

Regards

I think your logic may be flawed

ActiveSheet.Range("A1").Offset(dRow, dCol).Value = "."


?drow
 4 
?dcol
 3 
?Range("A1").Offset(dRow, dCol).Address
$D$5

So, when your destination row (dRow) variable has a value of 4, you put a dot in row 5.

Regards

Yep, that's definitely it. I've just stepped through a few more iterations and, for the rows not specifically checked, you put the dot in the next row. It is then overwritten when the specific row is processed.

I note that, for the rows specifically checked, you use dRow - 1

Regards

Thank you, I'll check it back when I go back home.

I use dRow - 1 to make it easier to manage the code, ie. I want dRow value to match the row that will actually be modified since I use Range("A1").Offset where the modified cell will always be in the next row from the the dRow value itself.

edit: Oh, dRow - 1 ! I'm such a dumb! I haven't even noticed that I wrote it without the "- 1" in the else condition. This is so embarassing. Thank you for your attention.

You're welcome. Thanks for the feedback.