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 !:thumb
thank you so much jindon.:yes

[Aussiebear]

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