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

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?

Your loop is going down rows, not across columns, and the SUMIF is hard
coded on columns. Is that correct?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James8309" wrote in message
...
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

On Jun 11, 5:19*pm, "Bob Phillips" wrote:
Your loop is going down rows, not across columns, and the SUMIF is hard
coded on columns. Is that correct?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"James8309" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Yes it is going down on rows, SUMIF is hard coded. How do I make it go
next column after it is done with the first one.