On Jun 11, 4:58*pm, James8309 wrote:
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

I simply added

For i = 2 to 41
COLCOUNT = i

and then added "Next" at the very end.

Is this correct?