I have a workbook with 13 sheets in it. 12 are the months of a year and the 13th is where I am doing all my calculations. Each month looks kind of like this:
Fruit Box 1 Box 2 Box 3
Apple 5 2 1
Orange 2 2 3
The code I wrote is supposed to look at the first fruit type on the 13th sheet, then go through each month sheet, look for the same fruit and if it exists add the value of the same row in the Box 2 column. After it has gone through all 12 months, put the total in the same row, next column to the fruit type on the 13th page. My code hangs up when it gets to the point of posting the value to the cell.
Sub FY_Appearance()
Dim lcalc As Long
With Application
lcalc = .Calculation
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim ArrayOne As Variant
ArrayOne = Array("Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept")
'
'
'Fruit Count
'
'
For Each Cel In Worksheets("Fruit Appearance").Range("A2:A" & LastRow)
x = 0
For Each Mnth In ThisWorkbook.Sheets(ArrayOne)
LR2 = Mnth.Cells(Rows.Count, 1).End(xlUp).Row
For Each Cel2 In Mnth.Range("A2:A" & LR2)
If Cel = Cel2 Then x = x + Cel2.Offset(0, 2).Value2
Next Cel2
Next Mnth
Worksheets("Fruit Appearance").Cells(Cel.Columns + 1).Value = x
Next Cel
End Sub
I have tried taking .Value in and out of different lines but it hasn't worked for me, at least not in any combination that I would expect to make sense. If you have a more efficient way of doing this, I'm all ears. Thanks for looking!
I figured it out finally. Thanks for considerations made. I realized that my variable Cel was already defined by worksheet location so mine was duplicate. I changed the line that was hanging up to:
Cel.Offset(0, 1).Value = x
Besides the duplicate, I probably didn't use columns right.