Odd behaviour from Arrays - can't find matches

[TheRedCardinal]

My code does the following:

• Loads in a range from a worksheet into an array
• Loads in a range from another worksheet into a separate array
• Loops through the first array in a specific column
• Searches a column in the second array and counts how many times it appears

The code looks like this:

        For Counter2 = 1 To UBound(IStat, 1)
        
            If FinalArray(Counter, a) = IStat(Counter2, b) Then Counter3 = Counter3 + 1
        
        Next Counter2

The variables a and b are used to identify the "columns" of the array and work ok.

The problem: I never get a value for Counter3 except 0.

Hovering over each expression in Step Through mode tells me that all parameters are working correctly, except that the search term in final array looks like a number (e.g. 9200) but in the search array, it is the same number inside quotation marks, so "9200". In fact, every line (including empties) in the search array is enclosed in "".

This is not returning a match.

I have tried:

• Formatting the whole source data for both arrays as text
• Formatting the specific columns as numbers

But so far to no avail.

Can anybody see a way around this?

 

[DanteAmor]

Hi @TheRedCardinal . I hope you are well.

It is difficult to provide help, since we have to assume where your data is, how your data is, etc.

It would be great if you put a sample of your data (use the xl2bb tool)

You must also put your complete macro.

Explaining with your data shows what you have and what you expect as a result.

------------------
Or maybe, if numbers:

If val(FinalArray(Counter, a)) = val(IStat(Counter2, b)) Then Counter3 = Counter3 + 1

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[TheRedCardinal]

Hi @TheRedCardinal . I hope you are well.

It is difficult to provide help, since we have to assume where your data is, how your data is, etc.

It would be great if you put a sample of your data (use the xl2bb tool)

You must also put your complete macro.

Explaining with your data shows what you have and what you expect as a result.

------------------
Or maybe, if numbers:

If val(FinalArray(Counter, a)) = val(IStat(Counter2, b)) Then Counter3 = Counter3 + 1

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

Thanks Dante Amor.

So your suggestion to use Val "worked" in the sense that it matched the numbers, but unfortunately that isn't a long term solution because I need to do other things later. So I need to understand why it is loading in data from Excel that is formatted as a number, as a string.

Here is a sample of the data (some columns removed and far fewer rows):

UK01 Intrastat - Baby Macro - Columns.xlsm
	A	B	C	D	E	F	G	H
3	Document number	Item	Higher-level item	Billing Type	Comm./imp. code no.	Invoice value	Currency	Statistical value
4	38003070	10	0	RE	3402399090	-5646.42	EUR	-5646.42
5	92077618	11	0	F2	2904100090	14863.5	EUR	14863.5
6	92077619	11	0	F2	29089900	15890.6	EUR	15890.6
7	92077645	21	0	F2	3402420090	79134	EUR	79134
8	92077649	11	0	F2	3402399090	33637.5	EUR	33637.5
9	92077656	11	0	F2	3402399090	21955.3	EUR	21955.3
10	92077657	11	0	F2	3402399090	2480	EUR	2480
11	92077675	11	0	F2	3402420090	71614	EUR	71614
12	92077686	11	0	F2	29041000	9300	EUR	9300
13	92077687	11	0	F2	3402501000	1480	EUR	1480
14	92077702	11	0	F2	3402399090	2130	EUR	2130
15	92077722	11	0	F2	29041000	11264	EUR	11264
16	92077723	11	0	F2	2904100090	15727.5	EUR	15727.5
17	92077724	11	0	F2	2904100090	11600	EUR	1450
18	92077725	11	0	F2	3402420090	4878	EUR	4878
19	92077734	11	0	F2	3402310000	57420	EUR	20880
20	92077737	11	0	F2	2904100090	20485	EUR	20485
21	92077738	11	0	F2	3402399090	19980	EUR	19980
22	92077744	11	0	F2	3402420090	14048	EUR	14048
23	92077745	11	0	F2	3402310000	59334	EUR	18879
24	92077752	11	0	F2	3402399090	26078	EUR	26078
25	92077772	11	0	F2	3402399090	11679	EUR	11679
26	92077773	11	0	F2	3402490000	9180	EUR	9180
27	92077773	21	0	F2	3402399090	15129.6	EUR	15129.6
Intrastat Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

This is the range loaded into the array IStat

And here is the data range that is loaded into the array

UK01 Intrastat - Baby Macro - Columns.xlsm
	A	B
3	Date	Invoice
4	01.05.2023	92077618
5	01.05.2023	92077619
6	01.05.2023	38003070
7	02.05.2023	92077645
8	02.05.2023	92077649
9	02.05.2023	92077656
10	02.05.2023	92077657
11	02.05.2023	92077675
12	02.05.2023	92077905
13	02.05.2023	92077930
14	03.05.2023	92077686
15	03.05.2023	92077687
16	03.05.2023	92077702
17	03.05.2023	92077737
18	04.05.2023	92077722
19	04.05.2023	92077723
Final Data

The column headed "document number" is formatted as "Number" yet when I load it into the array here:

IStat = WS4.Range(CellA, WS4.Cells(LastRow(WS4, CellA.Column), LastCol(WS4, CellA.Row)))

That "column" in the array now becomes a string.

 

[awoohaw]

I am not a VBA expert, but have you defined all the arrays as integers/long/double?

 

[TheRedCardinal]

Addendum:

I added this bit of code:

'********Temporary Fix************
'**Invoices were being loaded as strings so now looking to convert**

For Counter = 2 To UBound(IStat)

    IStat(Counter, 1) = Val(IStat(Counter, 1))

Next Counter

'********End of Temporary Fix***********

This has solved the issue so far, but I'd love to understand how to avoid it in full as I may need it again later.

 

[TheRedCardinal]

I am not a VBA expert, but have you defined all the arrays as integers/long/double?

Hi thanks,

They are all defined as Variants because they contain a mix of strings and numbers.

 

[DanteAmor]

This has solved the issue so far, but I'd love to understand how to avoid it in full as I may need it again later.

You must also put your complete macro.

Again you did not put your complete code
Could you please post all your code, that way I wouldn't have to assume anything.

 

[TheRedCardinal]

Again you did not put your complete code
Could you please post all your code, that way I wouldn't have to assume anything.

Sorry Dante Amor,

Here it is:

Sub MakeDispatchesTable()

Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, WS4 As Worksheet, WS5 As Worksheet
Dim Counter As Long
Dim VATArray As Variant, FinalArray As Variant, IStat As Variant

Set Wbk = ThisWorkbook
Set WS1 = Wbk.Sheets("Final Data")
Set WS2 = Wbk.Sheets("Settings")
Set WS3 = Wbk.Sheets("Start")
Set WS4 = Wbk.Sheets("Intrastat Data")
Set WS5 = Wbk.Sheets("VAT Report")

SCols = WS2.Range("DColumns")

'Populate Intrastat Array

Set CellA = WS4.UsedRange.Find("Document number")

IStat = (WS4.Range(CellA, WS4.Cells(LastRow(WS4, CellA.Column), LastCol(WS4, CellA.Row))))

'********Temporary Fix************
'**Invoices were being loaded as strings so now looking to convert**

For Counter = 2 To UBound(IStat)

    IStat(Counter, 1) = Val(IStat(Counter, 1))

Next Counter

'********End of Temporary Fix***********

Set CellA = WS5.UsedRange.Find("Tx")

Dim a As Long

a = CellA.Column
b = WorksheetFunction.CountIf(WS5.Columns(a), "=A3") - 1
c = WorksheetFunction.Match("A3", WS5.Columns(a), 0)
d = LastCol(WS5, a)

VATArray = WS5.Range(WS5.Cells(c, 1), WS5.Cells(b + c, d))
VATHeaders = WS5.Range(WS5.Cells(CellA.Row, 1), WS5.Cells(CellA.Row, d))

ReDim FinalArray(1 To UBound(IStat, 1), 1 To UBound(SCols, 1))

'Allocate headers

For Counter = 1 To UBound(SCols, 1)

    FinalArray(1, Counter) = SCols(Counter, 1)

Next Counter
    
'Redefine the DColumns Data with Array Locations

For Counter = 1 To UBound(SCols, 1)

    If SCols(Counter, 2) <> "" Then
    
        SCols(Counter, 2) = ArrayHeader(SCols(Counter, 2), VATHeaders)
    
    ElseIf SCols(Counter, 3) <> "" Then
    
        SCols(Counter, 3) = ArrayHeader(SCols(Counter, 3), IStat)
     
    End If
    
Next Counter
    
ArrRow = 2

For Counter = 1 To UBound(VATArray, 1) - 1

    For Counter1 = 1 To UBound(SCols, 1)

        If IsEmpty(SCols(Counter1, 2)) = False Then FinalArray(ArrRow, Counter1) = VATArray(Counter, SCols(Counter1, 2))
    
    Next Counter1

ArrRow = ArrRow + 1

Next Counter

'Populate the table with final data

a = ArrayHeader("Invoice", FinalArray)
b = ArrayHeader("Document number", IStat)

For Counter = 2 To UBound(FinalArray, 1)

    Counter3 = 0
    
    If FinalArray(Counter, 1) <> "" Then
        
        'Count how many times the invoice appears in the IStat file
        
        For Counter2 = 1 To UBound(IStat, 1)
        
            If Val(FinalArray(Counter, a)) = Val(IStat(Counter2, b)) Then Counter3 = Counter3 + 1
        
        Next Counter2
        
        Select Case Counter3
            
            Case 0
                
                'Do nothing?
                
            Case 1
                
                IStatData = WhereInArray(FinalArray(Counter, a), b, IStat)
                    
                For Counter4 = 1 To UBound(SCols, 1)
                        
                    If IsEmpty(SCols(Counter4, 3)) = False Then FinalArray(Counter, Counter4) = IStat(IStatData, SCols(Counter, 3))
                       
                Next Counter4
                    
            Case Is > 1
                
                ' To figure out
                
            End Select

    End If

Next Counter

WS1.Range("A3").Resize(UBound(FinalArray, 1), UBound(FinalArray, 2)) = FinalArray

End Sub

LastRow, LastCol and WhereinArray are functions I have built:

Function LastRow(WS As Worksheet, iCol)

    LastRow = WS.Cells(Rows.Count, iCol).End(xlUp).Row
    
End Function

Function LastCol(WS As Worksheet, iRow)

    LastCol = WS.Cells(iRow, Columns.Count).End(xlToLeft).Column
    
End Function

Function IsInArray(Search As Variant, Vector As Long, SearchArray As Variant) As Boolean

Dim MyCount As Long

IsInArray = False

For MyCount = LBound(SearchArray, Vector) To UBound(SearchArray, Vector)
    
    If SearchArray(MyCount, Vector) = Search Then IsInArray = True

Next MyCount

End Function

Function WhereInArray(Search As Variant, Vector As Variant, SearchArray As Variant) As Long

For MyCount = LBound(SearchArray, 1) To UBound(SearchArray, 1)

    If SearchArray(MyCount, Vector) = Search Then
        WhereInArray = MyCount
        Exit Function
    End If
    
Next MyCount

MyCount = 0

End Function
Function ArrayHeader(Search As Variant, SearchArray As Variant) As Long

For MyCount = LBound(SearchArray, 2) To UBound(SearchArray, 2)

    If SearchArray(1, MyCount) = Search Then
        ArrayHeader = MyCount
        Exit Function
    End If

Next MyCount

MyCount = 0
End Function

Function DoesSheetExist(Wbk As Workbook, SheetName As String) As Boolean

DoesSheetExist = False

For Each WS In Wbk.Worksheets

    If SheetName = WS.Name Then
        DoesSheetExist = True
        Exit Function
    End If

Next WS

End Function

Function WhereIn2DArray(Search As Variant, SearchArray As Variant) As Long

For MyCount = LBound(SearchArray) To UBound(SearchArray)

    If SearchArray(MyCount) = Search Then
        WhereIn2DArray = MyCount
        Exit Function
    End If
    
Next MyCount

MyCount = 0

End Function

 

[DanteAmor]

This is the range loaded into the array IStat

And here is the data range that is loaded into the array

These values appear to be text, are they really numbers in your sheet?

 

[TheRedCardinal]

That's the curious thing... these are fine as numbers in the array: