Little help please please!!!

  • Thread starter Thread starter James8309
  • Start date Start date
J

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
 
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
Click to expand...

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)
 
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 -
Click to expand...

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