Hi, everyone

VBA code below works perfectly except after performing sumif on column
B:B it doesn't move on to the next column. I think I am missing a line
or something. Probably Next function not functioning.

Can anyone help?

Thanks alot



Sub Testing_final2()


Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim FNAME As String
Dim SUMREF As String
Dim COLCOUNT As Long


MYPATH = "C:\Doc\My Documents\"
LR = Range("A" & Rows.Count).End(xlUp).Row


Set ThisSht = Workbooks("Sumif testing file - 11 June
2008.xls").Sheets("Sheet1")
COLCOUNT = 2 'column B
With ThisSht
FNAME = MYPATH & .Range("A1").Value & "\" & _
Year(.Cells(5, COLCOUNT).Value) & "\" & _
Format(.Cells(5, COLCOUNT).Value, "MMM YY")
Debug.Print FNAME
FNAME = FNAME & ".XLS"
Set WB = Workbooks.Open(Filename:=FNAME)
Debug.Print FNAME


For Each CELL In .Range(.Cells(6, COLCOUNT), .Cells(LR, COLCOUNT))
SUMREF = .Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 33
CELL.Value = Application.WorksheetFunction. _
SumIf(WB.Sheets("Sheet1").Range("H:U"), _
SUMREF, WB.Sheets("Sheet1").Range("U:U"))
Next CELL
WB.Close
COLCOUNT = COLCOUNT + 1

End With

End Sub