Sum Data in bad format

[asherd]

Data is in following format unfortunately. I'm trying to come up with something that says" Go down the column and add up all the sales for Region 1, then Region 2 etc. Data goes down in this way for 7500 rows
If the data were in database form no problem, but here, YECHHH!! Any ideas would be helpful, i've tried a bunch of ways..........

Region 1
Year 2000 2001
Sales 100 400
Expenses 50 300
Income 50 100
-----------------------
Region 2
Year 2000 2001
Sales 100 400
Expenses 50 300
Income 50 100

 

[xlbo]

would something like this work ??

Sub GetSales()
i = 1
For x = 1 To UsedRange.Rows.Count
If Range("A" & x).Text = "Region" Then 'new region
If i <> 1 Then
With Sheets(&quot;Sheet2&quot
.Range(&quot;B&quot; & i).Value = Sales2000
.Range(&quot;C&quot; & i).Value = Sales2001
.Range(&quot;A&quot; & i + 1).Value = &quot;Region&quot; & i
End With
Else
End If

i = i + 1
Sales2000 = 0
Sales2001 = 0
ElseIf Range(&quot;A&quot; & x) = &quot;Sales&quot; Then
Sales2000 = Sales2000 + Range(&quot;B&quot; & x).Value
Sales2001 = Sales2001 + Range(&quot;C&quot; & x).Value
Else
End If
Next x
End Sub


This assumes
1:that there could be more than 1 row of sales data per region
2:that all ergional data will be together
3ata will be entered onto a seperate sheet
4: Yearly data is kept seperate

I have only coded for 2000 / 2001 but it would be very easy to accomodate more years Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[asherd]

Thanks. I'll give it a go...............