PDA

View Full Version : Little help with Autofill please



jaedong1221
06-04-2008, 04:25 PM
:help Hi Everyone.

I am having difficulties trying to fit in Autofill codes in between my
sumif macro.

My sumif macro works fine and it does;
1. Open up correct data workbook from specific directory then.

2. Do a sumif in each cell Range from B6:AO6 to the Last row that
contains data in Column A. Values in Column A is my criteria range for sumifs.

3. Close the data workbook.

Basically it opens up a correct file, perform a sumif then close
workbook.

Because my macro only does one cell at a time, if I have values in Column A from A6 to A200 for an example, it will take too long!:doh:

That is why I am just trying to fit 'Autofill' in. so instead of closing
the data workbook straight away after performing sumif in each cell,
it does autofill for that column then closes the workbook.



Where do I squeeze the autofill in? or is there better way of doing this?

Please help and thank you !!! in advance!



Sub Test3()

Dim CELL As Range
Dim LR As Integer
Dim MYPATH As String
Dim WB As Workbook
Dim MYREF As String
Dim SUMREF As String
Dim Lastrow As Long
Dim WS As Worksheet
MYPATH = "C:\Mydocuments\ABC\"
LR = Range("A65000").End(xlUp).Row
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For Each CELL In Range("B6:AO6")
CELL.Select
SUMREF = Range("A" & CELL.Row).Value
CELL.Interior.ColorIndex = 25
MYPATH = MYPATH & Range("A1").Value & "\" & Year(Cells(5,
ActiveCell.Column).Value) & "\" _
& Format(Cells(5, ActiveCell.Column).Value, "MMM YY")
Debug.Print MYPATH
MYREF = MYPATH & ".xls"
Workbooks.Open Filename:=MYREF
Debug.Print MYREF
Set WB = ActiveWorkbook
CELL.Value =
Application.WorksheetFunction.SumIf(WB.Sheets("Sheet1").Range("H:U"),
SUMREF, WB.Sheets("Sheet1").Range("U:U"))

MYPATH = "C:\Mydocuments\ABC\"
WB.Close
Next
End Sub


Edit: VBA tags added

lucas
06-04-2008, 05:22 PM
Hi James. Select your code when posting and hit the VBA button to format your it as I have done to your code in post 1.

jaedong1221
06-04-2008, 06:08 PM
wow Kewl

No worries!

Bob Phillips
06-05-2008, 12:39 AM
You can't use autoflll because you are not filling anything, you are calculating a single SUMIF per workbook, so it is difficult to see where you can do block processing.

jaedong1221
06-05-2008, 04:06 PM
Is there any other way where I can do autofill or auto calculate the whole column before closing the workbook?

Thanks for your help.