mismatch error when use headers by arabic language

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

Code:

---

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

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) .

Code:

---

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?

Maybe this amended version might help?

Code:

---

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

---

Perhaps

change

Code:

---

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

---

to

Code:

---

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

---

@Aussiebear
unfortunately doesn't work!

that works perfectly !:thumb
thank you so much jindon.:yes

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