Little help please please!!!

[James8309]

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

 

[James8309]

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?

 

[Bob Phillips]

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]

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)
















- 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.