Runtime error: 13 mismatch in complicated VBA array

[Event2020]

Windows 10
Excel pro Plus 2019


I am getting a Run-Time: error 13 - Data Mismatch when running my code below with excel VBA editor highlighting the two rows

If arrData(i, 1) = arrData(i + 1, 1) And arrData(i, 2) = arrData(i + 1, 2) And _
arrData(i, 9) = arrData(i + 1, 9) And arrData(i, 10) = arrData(i + 1, 10) Then

I wanted to achieve the fastest possible method using VBA as using a formula (which I explain further down) takes excel over 6
minutes to run on a spreadsheet with currently 91,000 rows and when live, this figure will only increase.

What is it I am running the Sub on.
On a worksheet called "DATA SORT" with a header row.
There is data in Columns A to J.

For the sake of this explination, a number of rows with matching data in specific columns are essentually a data set for one product.

The defining Columns where matching data will occur are Columns A, B, I2 and J.
The data in Columns C, D, E, F, and G, will not match and will vary.
This is why I am using columns A, B, I2 and J to make the matches.

What is it that I am trying to do.
Where the data (or value) in Columns A, B, I2 and J match with the next row down, look at the row after that, if it also matches then
continue until the next row does not match.

*In essence that is defining the a set or group of rows that relate to each other because of the matches in Columns A, B, I2 and J.

For that set or group of "matching" rows, look at the data or values in the rows of Column E.
*Column E value will always be a number, either a 1 or 2 or a mix of 1's and 2's.

If the data/values in all of the cells are all 1's then in column K, in the last row of the matching data set enter the text "YES"
If the data/values in all of the cells are all 2's then in column K, in the last row of the matching data set enter the text "NO"
If the data/values in all of the cells are a mix or 1's and 2' then in column K, in the last row of the matching data set enter the text "MAYBE"

Then look at the row that did not match with the one above it, and where the data (or value) in Columns A, B, I2 and J match with the next row down, look at the row after that, if it also matches then continue down until the next row does not match.

*This defines the next Data Set (product family) repeat the process described above.
Then move on to the next, and then the next all of the way down the work sheet.

I am mindful to not confuse my description but I was origianlly using a formula to do this which was entered by VBA and copied down column K
until the last used row.

This was fine when the number of rows was fairly small, say around 1000, maybe 2000, but now there are 91,000 and this will get bigger, when pasted in and copied down by VBA it takes over 6 minutes to run
and the pages of the work sheet go dim - not good really.

I include it here so you might be able to see what I am trying to replace but this VBA was my attempt to replace the formula below with something faster.
My thinking was that loading as much of the data, that my knowledge alllows me to, in to an array and doing the calculations in memory *should* speed
up quite a lot.


=IF(AND(A2=A3,B2=B3,I2=I3,J2=J2),"",TEXTJOIN("+",,CHOOSE({1,2},IF(COUNTIFS(E$2:E2,1,B$2:B2,B2),"YES",""),IF(COUNTIFS(E$2:E2,2,B$2:B2,B2),"NO",""),2)))


This is kind of a big ask but I have tried to explain what I am trying do and what I have done to get as best as I can.


I am extremely grateful for any assistance.



Sub SortAndWriteLettersClmK()
'Turn off application settings
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim ws As Worksheet
Dim lastRow As Long, i As Long, startRow As Long
Dim matchCountOne As Long, matchCountTwo As Long
Dim arrData As Variant, results() As String

Set ws = ThisWorkbook.Worksheets("DATA SORT")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Read data into an array for faster processing
arrData = ws.Range("A2:J" & lastRow).value
ReDim results(1 To UBound(arrData))

'Initialize startRow
startRow = 1

For i = 1 To UBound(arrData) - 1
If arrData(i, 1) = arrData(i + 1, 1) And arrData(i, 2) = arrData(i + 1, 2) And _
arrData(i, 9) = arrData(i + 1, 9) And arrData(i, 10) = arrData(i + 1, 10) Then
'Matching row found, increment counts based on value in column E (position 5 in array)
If arrData(i, 5) = 1 Then
matchCountOne = matchCountOne + 1
ElseIf arrData(i, 5) = 2 Then
matchCountTwo = matchCountTwo + 1
End If
Else
'Non-matching or final match in a series - determine what to write in column K
If matchCountOne > matchCountTwo Then
FillResults results, startRow, i, "YES"
ElseIf matchCountTwo > matchCountOne Then
FillResults results, startRow, i, "NO"
ElseIf matchCountOne = matchCountTwo And matchCountOne > 0 Then
FillResults results, startRow, i, "MAYBE"
End If
'Reset counters and set new start row
matchCountOne = 0
matchCountTwo = 0
startRow = i + 1
End If
Next i

'Write results to Column K
ws.Range("K2:K" & lastRow).value = Application.Transpose(results)
'Turn on application settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Sub FillResults(arr() As String, start As Long, endRow As Long, value As String)
'Helper sub
Dim i As Long
For i = start To endRow
arr(i) = value
Next i
End Sub

[June7]

Maybe time to migrate to a database app like Access.

Could you provide sample workbook? Follow instructions at bottom of my post.

[georgiboy]

Perhaps it will work better as below:


Sub SortAndWriteLettersClmK()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim matchCountOne As Long, matchCountTwo As Long
Dim arrData As Variant, results() As String

'Turn off application settings
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set ws = ThisWorkbook.Worksheets("DATA SORT")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

'Read data into an array for faster processing
arrData = ws.Range("A2:J" & lastRow).value
ReDim results(1 To UBound(arrData))

For i = 1 To UBound(arrData) - 1
If arrData(i, 1) = arrData(i + 1, 1) And arrData(i, 2) = arrData(i + 1, 2) And _
arrData(i, 9) = arrData(i + 1, 9) And arrData(i, 10) = arrData(i + 1, 10) Then
'Matching row found, increment counts based on value in column E (position 5 in array)
If arrData(i, 5) = 1 Then
matchCountOne = matchCountOne + 1
ElseIf arrData(i, 5) = 2 Then
matchCountTwo = matchCountTwo + 1
End If
Else
'Non-matching or final match in a series - determine what to write in column K
If matchCountOne > 0 And matchCountTwo = 0 Then
results(i) = "YES"
ElseIf matchCountTwo > 0 And matchCountOne = 0 Then
results(i) = "NO"
Else
results(i) = "MAYBE"
End If
'Reset counters and set new start row
matchCountOne = 0
matchCountTwo = 0
End If
Next i

'Write results to Column K
ws.Range("K2:K" & lastRow).value = Application.Transpose(results)
'Turn on application settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[jdelano]

When it stops, what are the values of each of the arrData items you're attempting to compare? Are they each the same data type?
You can use these instructions Debugging in Excel VBA (In Easy Steps) (excel-easy.com) (https://www.excel-easy.com/vba/examples/debugging.html) to view their contents at the time of the error, this should show you why the error is happening.

[Event2020]

Hello Everyone.

First I would like to thank everyone who has either read this thread and/or replied but I wish to apoligise
for not replying until now but since making this post I have been quite unwell.

Before becoming ill I had made a start on a Example worksheet with my existing VBA in use but now
I am on the mend I hope to get that completed and make replies to the posts here over the next few days.

Once again, thank you for your replies and suggestions.

Best

Event2020

[jdelano]

Very glad to know you're on the mend. Sorry you've been unwell.

[Jan Karel Pieterse]

I bet one or more cells in the original data contain an error result (like #REF!, #DIV/0!, #NAME?). You could convert the values to string before comparing:

If CStr(arrData(i, 1)) = CStr(arrData(i + 1, 1)) And CStr(arrData(i, 2)) = CStr(arrData(i + 1, 2)) And _
CStr(arrData(i, 9)) = CStr(arrData(i + 1, 9)) And CStr(arrData(i, 10)) = CStr(arrData(i + 1, 10)) Then

[Event2020]

Very glad to know you're on the mend. Sorry you've been unwell.

Thank you, that is very kind.

Tentatively back to full speed today.

[Event2020]

When it stops, what are the values of each of the arrData items you're attempting to compare? Are they each the same data type?
You can use these instructions Debugging in Excel VBA (In Easy Steps) (excel-easy.com) (https://www.excel-easy.com/vba/examples/debugging.html) to view their contents at the time of the error, this should show you why the error is happening.


Thank you.

[Event2020]

Hi everyone.


The Runtime error: 13 seems to have, strangly, cured itself and I am unable to duplicate it.
Ihave not changed the code or the dataset.


I have attached a link to an example workbook to this post which uses dummy data, mostly fruit and various varieties, all obtained from various websites and the important colums that must match moving down the worksheet, A, B, I, J, are intact regardless of the dummy data.

Its in a 3.4 mb zip file so I am unable to upload as a file here.

https://drive.google.com/file/d/1yJdV7ZSj-6C9PwMpusq2o6hXAozojflU/view?usp=sharing



I have added two extra colums, L & M.
In my original worksheet I only used Column K to host the value from my formula to VBA conversion.


I wanted a side-by-side comparason while testing so it now works like this...
Column K = original formula
Column L = why georgiboy's Code
Column M = My original code


georgiboy's code works perfectly, and fast, except for when it gets to Row L21425 and it fills every cell going down with "#N/A" as my original does. The VBA itself does not give any error except from entering "#N/A" in the cells.


I know in my original post I mentioned this error but in the example workbook I am using all new data so that rules out a data error. I am at a loss as to why georgiboy's Code and my own produces this error at the same row number.

I am wondering is this might be a virtual memory limitation.


If you are kind enough to take a look at the Example worksheet, you will see that I have left comments for myself in the VBA. I do this as I work to remind myself of issues and so on.

Thanks

[georgiboy]

I think this is due to the limitations of the TRANSPOSE function, try it as below where the need for this function has been removed.


Sub FormulaInVBA_Col_L()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim matchCountOne As Long, matchCountTwo As Long
Dim arrData As Variant, results As Variant

'Turn off application settings
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set ws = ThisWorkbook.Worksheets("Test Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

'Read data into an array for faster processing
arrData = ws.Range("A2:J" & lastRow).value
ReDim results(1 To UBound(arrData), 1 To 1)

For i = 1 To UBound(arrData) - 1
If arrData(i, 1) = arrData(i + 1, 1) And arrData(i, 2) = arrData(i + 1, 2) And _
arrData(i, 9) = arrData(i + 1, 9) And arrData(i, 10) = arrData(i + 1, 10) Then
'Matching row found, increment counts based on value in column E (position 5 in array)
If arrData(i, 5) = 1 Then
matchCountOne = matchCountOne + 1
ElseIf arrData(i, 5) = 2 Then
matchCountTwo = matchCountTwo + 1
End If
Else
'Non-matching or final match in a series - determine what to write in column K
If matchCountOne > 0 And matchCountTwo = 0 Then
results(i, 1) = "YES"
ElseIf matchCountTwo > 0 And matchCountOne = 0 Then
results(i, 1) = "NO"
Else
results(i, 1) = "MAYBE"
End If
'Reset counters and set new start row
matchCountOne = 0
matchCountTwo = 0
End If
Next i

'Write results to Column K
ws.Range("L2:L" & lastRow - 1).value = results
'Turn on application settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Event2020]

I think this is due to the limitations of the TRANSPOSE function, try it as below where the need for this function has been removed.


Hi georgiboy.

I sat in front of my laptop for nearly five hours and I just could not see what was wrong.

I would never have thought of the TRANSPOSE function being the issue as I was, wrongly, blinkered in the
mindset of a literal conversion of the formula to VBA without considering the steps needed.


This afternoon I have tested it in my example workbook, and in the final workbook.

Thank you, it works perfectly and is almost instantaneous filling in data down 91,304 rows.

I could not have asked for a better result.

Thank you again for your kind help.

[Aussiebear]

Can the limitations be overcome by reducing the size of data being transposed at the time of action?

[georgiboy]

Can the limitations be overcome by reducing the size of data being transposed a the time of action?

I suppose, but sometimes the data is the data and you just have to work with it. If I am honest the code is better without the use of TRANSPOSE.

TRANSPOSE is limited to the same amount of rows as old excel I believe.

[Paul_Hossler]

I try to find a MS article for things like this, but this is the best I found


https://www.ablebits.com/office-addins-blog/transpose-excel-rows-columns/#:~:text=Transpose%20data%20in%20Excel%20using%20VBA%20macro&text=Transposing%20with%20VBA%2C%20has%20a%20limitation%20of%2065536%20elem ents.



Note. Transposing with VBA, has a limitation of 65536 elements. In case your array exceeds this limit, the extra data will be silently cast away.

[Aflatoon]

If I remember rightly, it varies with versions too. In my 365, this code:


Dim u(1 To 2, 1 To 70000)
Dim v
v = Application.Transpose(u)
MsgBox UBound(v, 1)


rather worryingly shows 4464 as the upper bound of the first dimension of the transposed array. So it appears to have done some sort of Mod 65536 and resized using the remainder, keeping the data from the start of the original array.

[Paul_Hossler]

I wonder if 32 bit or 64 bit Excel makes a difference?

[p45cal]

I wonder if 32 bit or 64 bit Excel makes a difference?

With 64bit Windows and 64bit MS365 I get the same results as msg#16
For me, I try always to avoid using TRANSPOSE in vba;

it's faster to loop and
transpose converts the data type Date to String, leaving it to Excel and whatever locale it's working in to re-interpret those date strings to dates proper.

[georgiboy]

Mine is the same - 4464,

I did note though that if the number of rows is 65536 then the ubound returns 65536. If you make it 65537 then the ubound is 2 I suppose for the same reason.


Dim u(1 To 2, 1 To 65536)
Dim v
v = Application.Transpose(u)
MsgBox UBound(v, 1)


Dim u(1 To 2, 1 To 65537)
Dim v
v = Application.Transpose(u)
MsgBox UBound(v, 1)

I think I will be avoiding TRANSPOSE on larger ranges that have the potential to expand going forward, why on earth would Microsoft not have the transpose function updated to work with the new row size...


So it appears to have done some sort of Mod 65536 and resized using the remainder, keeping the data from the start of the original array.
So strange

[Paul_Hossler]

Guessing ---

Older Excel worksheets were limited to 65536 rows, and since TRANSPOSE() is a ws fuction, someone took the easy way out and hardcoded something that was never made compatible with the 1M+ row versions of Excel??