Array output - very very strange - I am a complete loss

[JeffGrant]

Hi all, I don't know if I am going to explain this one well at all. But here goes.

I have an array that creates a unique ID, by joining 3 array columns. Here is the VBA code.

Sub CreateUniqueBDIF()
Dim arr As Variant, rng As Range
arr = Sheet61.Range("A1").CurrentRegion

Dim i As Long
Set rng = Sheet61.Range("R1:AD1")
For i = LBound(arr, 1) + 1 To UBound(arr, 1)
    
    'test for 1 or 2 digit race numbers
    If Mid(Range("b" & i), 3, 1) = " " Then
        'one digit race number
        arr(i, 12) = Mid(Range("b" & i), 2, 1)
        Else
        'two digit race number
        arr(i, 12) = Mid(Range("b" & i), 2, 2)
    End If
    
    'Create the unique Betfair ID
    arr(i, 13) = arr(i, 3) & arr(i, 12) & arr(i, 9)
Next i


Sheet61.Range("R1").CurrentRegion.ClearContents
Dim rowCount As Long, columnCount As Long
rowCount = UBound(arr, 1)
columnCount = UBound(arr, 2)

Sheet61.Range("R1").Resize(rowCount, columnCount).Value = arr

End Sub

As part of the concatenation, column B is tested for a single or double digit race number. The idea being to have no spaces in the final concatenated ID. No drama there. It works like a treat.

The data in column B, looks like this and this is where the race number comes from. No problem there either.

market_name

R6 1400m Mdn

R6 1400m Mdn

R6 1400m Mdn

R6 1400m Mdn

This data is downloaded as part of an API to a data supplier. - No problem there.

When I run the sub above, Sub CreateUniqueBDIF, as a manual start, everything works normally, the unique IDs are created correctly and pull out the number 6 in the above data correctly. No Problem there.

However, If I run the CreateUniqueBDIF sub as part of another sub to group everything together, as in the sub below, the above code that pulls out the number 6, substitutes the number 6, and any other number in the data set for the text "Da". But as you can see, there is no "Da" in "R6 1400m Mdn". I have absolutely no idea where the array construction of arr(i, 13) = arr(i, 3) & arr(i, 12) & arr(i, 9) is getting the "Da" from. "Da" does not exist anywhere.

Sub ManualUpdate()
    'Set Current Time
    Call SetCurrentTime
    'Run Download Script
    Call RunAPIScript
    'Application.Wait Now + TimeValue("00:00:20")
    Call CreateUniqueBDIF
End Sub

I have tried any number of Application.Wait values and it makes no difference.

So basically, if I run the code in Sub ManualUpdate, I get a "Da" in the unique ID. If I run the Sub Create UniqueBDIF, separately it works every time.

I am absolutely at a loss on this one because I need to create the ID's on the run. Manual intervention is not really an option.

As always any and all guidance is very much appreciated.

 

[rlv01]

I suspect your used of unqualified ranges is causing you problems. For example, this range statement is unqualified (no worksheet specified), so excel has to guess.

arr(i, 12) = Mid(Range("b" & i), 2, 2)

instead of this qualifed range

arr(i, 12) = Mid(Sheet61.Range("b" & i), 2, 2)

When a range like Range("b" & i) does not have the worksheet specified, VBA assumes the worksheet is the active sheet, so Range("b" & i) is the same as ActiveSheet.Range("b" & i). Which is probably OK when you run it manually since the active sheet is likely Sheet61. But when you call it after running other scripts, who knows what the activesheet is, so you should explicitly define it.

 

[Peter_SSs]

Further to what @rlv01 has said,

1. Consider using With ... End With instead of continually repeating Sheet61
2. Use the values already read into the arr array instead of going back to column B in the worksheet to check for the race numbers. More efficient/faster that way.
3. You can get the 1 or 2 digit race number directly if, as it seems from your code, the race number is followed by a space character.
4. It is not a requirement but my preference is to put all my variable declarations at the start of the procedure rather than scattered throughout.
5. In the suggested code below I have commented the two parts referring to rng since that is not used anywhere else in this code. I left the code there though in case there is actually more to this procedure where rng is actually used.

Sub CreateUniqueBDIF_v2()
  Dim arr As Variant ', rng As Range
  Dim i As Long, rowCount As Long, columnCount As Long
  
  With Sheet61
    arr = .Range("A1").CurrentRegion
'    Set rng = .Range("R1:AD1")
    For i = LBound(arr, 1) + 1 To UBound(arr, 1)
        
        'Get 1 or 2 digit race numbers
        arr(i, 12) = RTrim(Mid(arr(i, 2), 2, 2))
        
        'Create the unique Betfair ID
        arr(i, 13) = arr(i, 3) & arr(i, 12) & arr(i, 9)
    Next i
    
    .Range("R1").CurrentRegion.ClearContents
    rowCount = UBound(arr, 1)
    columnCount = UBound(arr, 2)
    .Range("R1").Resize(rowCount, columnCount).Value = arr
  End With
End Sub

 

[JeffGrant]

Thank you folks. Nailed it !!!!!
rlvo1 - I guess I was looking too hard but couldn't see what was in front of me!!!
Peter_SSs, I made your suggested changes and can definitely see the change in speed with large sets on the weekend.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.