mismatch error when use headers by arabic language

[maghari]

Hi,
the code works well with English headers , but when try to Arabic language then will show mismatch error in this line

b(nRow, 5) = b(nRow, 3) * b(nRow, 4)

so what suppose the code does it in SH1 sheet I would match headers with headers in others sheets and populate data under header and combine duplicates Brand based on column G if contains the same price in column D .
without forgetting add new row before TOTAL row to sum column F .I will add new sheets before SH1 sheet.
I put the result what code should do to understand my problem.
thanks

[Aussiebear]

It seems strange that this line would cause the error, as it is written calculates the product of the accumulated quantity (column 3 of b) and the value from column "D" (price) and stores it in the fifth column of "b" (an array) .

b(nRow, 5) = b(nRow, 3) * b(nRow, 4)

When you say so have changed the English headers to Arabic, nothing else changed within the sheet?


Sorry late question. Are you sure the values in columns are truely numeric?

[Aussiebear]

Maybe this amended version might help?

Sub Populate_data()
    Dim sh As Worksheet, sh1 As Worksheet
    Dim idx As Long, lr As Long, i As Long, y As Long, nRow As Long
    Dim dic As Object
    Dim a As Variant, b As Variant
    Dim tQty As Double, tBal As Double
    Dim ky As String 
    Set sh1 = Sheets("SH1")
    Set dic = CreateObject("Scripting.Dictionary")
    For idx = 1 To Sheets.Count
        Set sh = Sheets(idx)
        If sh.Name <> sh1.Name Then
            lr = lr + sh.Range("G" & Rows.Count).End(3).Row
        End If
    Next
    ReDim b(1 To lr, 1 To 5)
    For idx = 1 To Sheets.Count
        Set sh = Sheets(idx)
        If sh.Name <> sh1.Name Then
            a = sh.Range("A2", sh.Range("G" & Rows.Count).End(3)).Value
            For i = 1 To UBound(a, 1)
                If a(i, 1) <> "TOTAL" Then
                    ky = a(i, 7) & "|" & a(i, 4)
                    If Not dic.exists(ky) Then
                        y = y + 1
                        dic(ky) = y
                    End If
                    nRow = dic(ky)
                    b(nRow, 1) = nRow
                    b(nRow, 2) = a(i, 7)
                    b(nRow, 3) = b(nRow, 3) + a(i, 5)
                    Dim qty As Double
                    Dim price As Double
                    qty = CDbl(a(i, 5))
                    price = CDbl(a(i, 4))
                    b(nRow, 4) = a(i, 4)
                    b(nRow, 5) = b(nRow, 3) * price
                    tQty = tQty + qty
                    tBal = tBal + (price * qty)
                End If
            Next
        End If
    Next
    Application.ScreenUpdating = False
    sh1.Range("B22:F" & Rows.Count).Clear
    sh1.Range("B22").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    lr = sh1.Range("C" & Rows.Count).End(3).Row
    With sh1.Range("B" & lr + 1)
        .Value = "TOTAL"
        .Font.Bold = True
        .Offset(0, 2).Value = tQty
        .Offset(0, 4).Value = tBal
    End With
    With sh1.Range("D22:F" & lr + 1)
        .NumberFormat = "#,##0.00"
    End With
    With sh1.Range("B22:F" & lr + 1)
        .HorizontalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

[jindon]

Perhaps

change

        If a(i, 1) <> "TOTAL" Then

to

        If (a(i, 1) = "") + (IsNumeric(a(i, 1))) Then

[maghari]

@Aussiebear
unfortunately doesn't work!

[maghari]

that works perfectly !
thank you so much jindon.

[Aussiebear]

If ever I get out of here, I'm heading to Japan to learn from Jindon.