How to best resolve a runtime error 6 on sales calculator with VBA?

I have a data set of several stores and my attempt is to be able to calculate each store independantly.

Important to note that sales are in JP Yen and thus are in the thousands for each sale. So I ran the following code in VBA.

Dim siteID As String, total As Integer, sheet As Worksheet, i As Integer
total = 0
siteID = InputBox("Enter the SiteID name (case sensitive)")
For Each sheet In Worksheets
    For i = 2 To 1000
        If sheet.Cells(i, 2).Value = siteID Then
            total = total + sheet.Cells(i, 26).Value
        End If
    Next i
Next sheet
MsgBox "total sales of " & siteID & " is " & total

As a result I have received runtime e error 6

I jiggied with this a little and realised its because the result has too many characters. Eventually what I did was to convert Yen to USD and then the code worked!

I want to know if there's a more elegant way to do this so the result stays in JPY and so I don't have to convert manually to USD.

Change your "total" data type from Integer to Long, I'm guessing you're overflowing what Integer can hold.