Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help on Autofill please
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 B7:AO7 to the Last row that contains data with criteria on column A 3. Close data workbook. Basically it opens up a correct file, perform a sumif then close workbook. 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? I was thinking after the "CELL.Value = Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line. 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("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U")) MYPATH = "C:\Mydocuments\ABC\" WB.Close Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help on Autofill please
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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow For Each CELL In Range("B" & RowCount & ":AO" & RowCount) SUMREF = CELL.Offset(0, -1) CELL.Interior.ColorIndex = 25 MYPATH = Folder & Range("A1").Value & "\" & _ Year(Cells(5, CELL.Column).Value) & "\" _ & Format(Cells(5, CELL.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")) WB.Close Next CELL Next RowCount End Sub "James8309" wrote: 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 B7:AO7 to the Last row that contains data with criteria on column A 3. Close data workbook. Basically it opens up a correct file, perform a sumif then close workbook. 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? I was thinking after the "CELL.Value = Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line. 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("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U")) MYPATH = "C:\Mydocuments\ABC\" WB.Close Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help on Autofill please
On Jun 4, 5:38*pm, Joel wrote:
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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow * *For Each CELL In Range("B" & RowCount & ":AO" & RowCount) * * * SUMREF = CELL.Offset(0, -1) * * * CELL.Interior.ColorIndex = 25 * * * MYPATH = Folder & Range("A1").Value & "\" & _ * * * * *Year(Cells(5, CELL.Column).Value) & "\" _ * * * * *& Format(Cells(5, CELL.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")) * * * WB.Close * *Next CELL Next RowCount End Sub "James8309" wrote: 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 B7:AO7 to the Last row that contains data with criteria on column A 3. Close data workbook. Basically it opens up a correct file, perform a sumif then close workbook. 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? I was thinking after the "CELL.Value = Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line. 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("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U")) * * * * *MYPATH = "C:\Mydocuments\ABC\" * * * * * WB.Close * * * * Next End Sub- Hide quoted text - - Show quoted text - It still calculates sum for each cell 'one by one' and it takes too long. Is it possible to do a sumif on one cell then autofill(autocalculate) the whole row to the last row? then close workbook? i.e. if Column A has values from A6 to A100, in Cell B6, it will open up data workbook, perform a sumif then autofill from B6 to B100 then close workbook. Same process through B to AO |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help on Autofill please
when you use a worksheet function from VBA it take the same time to run as if
you put the function on the worksheet try this code instead 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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow For Each CELL In Range("B" & RowCount & ":AO" & RowCount) SUMREF = CELL.Offset(0, -1) CELL.Interior.ColorIndex = 25 MYPATH = Folder & Range("A1").Value & "\" & _ Year(Cells(5, CELL.Column).Value) & "\" _ & Format(Cells(5, CELL.Column).Value, "MMM YY") Debug.Print MYPATH MYREF = MYPATH & ".xls" Workbooks.Open Filename:=MYREF Debug.Print MYREF Set WB = ActiveWorkbook subtotal = 0 for each T_Cell in WB.Sheets("Sheet1"). _ Range("H" & Rowcount & ":U" & Rowcount) if T_Cell.value = SUMREF then subtotal = subtotal + T_Cell.value end if next T_Cell CELL.Value = subtotal WB.Close Next CELL Next RowCount End Sub "James8309" wrote: On Jun 4, 5:38 pm, Joel wrote: 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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow For Each CELL In Range("B" & RowCount & ":AO" & RowCount) SUMREF = CELL.Offset(0, -1) CELL.Interior.ColorIndex = 25 MYPATH = Folder & Range("A1").Value & "\" & _ Year(Cells(5, CELL.Column).Value) & "\" _ & Format(Cells(5, CELL.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")) WB.Close Next CELL Next RowCount End Sub "James8309" wrote: 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 B7:AO7 to the Last row that contains data with criteria on column A 3. Close data workbook. Basically it opens up a correct file, perform a sumif then close workbook. 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? I was thinking after the "CELL.Value = Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line. 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("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U")) MYPATH = "C:\Mydocuments\ABC\" WB.Close Next End Sub- Hide quoted text - - Show quoted text - It still calculates sum for each cell 'one by one' and it takes too long. Is it possible to do a sumif on one cell then autofill(autocalculate) the whole row to the last row? then close workbook? i.e. if Column A has values from A6 to A100, in Cell B6, it will open up data workbook, perform a sumif then autofill from B6 to B100 then close workbook. Same process through B to AO |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Little help on Autofill please
On Jun 5, 10:12*am, Joel wrote:
when you use a worksheet function from VBA it take the same time to run as if you put the function on the worksheet try this code instead 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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow * *For Each CELL In Range("B" & RowCount & ":AO" & RowCount) * * * SUMREF = CELL.Offset(0, -1) * * * CELL.Interior.ColorIndex = 25 * * * MYPATH = Folder & Range("A1").Value & "\" & _ * * * * *Year(Cells(5, CELL.Column).Value) & "\" _ * * * * *& Format(Cells(5, CELL.Column).Value, "MMM YY") * * * Debug.Print MYPATH * * * MYREF = MYPATH & ".xls" * * * Workbooks.Open Filename:=MYREF * * * Debug.Print MYREF * * * Set WB = ActiveWorkbook * * * subtotal = 0 * * * for each T_Cell in WB.Sheets("Sheet1"). _ * * * * *Range("H" & Rowcount & ":U" & Rowcount) * * * * *if T_Cell.value = SUMREF then * * * * * * subtotal = subtotal + T_Cell.value * * * * *end if * * * next T_Cell * * * CELL.Value = subtotal * * * WB.Close * *Next CELL Next RowCount End Sub "James8309" wrote: On Jun 4, 5:38 pm, Joel wrote: 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 Folder = "C:\Mydocuments\ABC\" Lastrow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = 6 To Lastrow * *For Each CELL In Range("B" & RowCount & ":AO" & RowCount) * * * SUMREF = CELL.Offset(0, -1) * * * CELL.Interior.ColorIndex = 25 * * * MYPATH = Folder & Range("A1").Value & "\" & _ * * * * *Year(Cells(5, CELL.Column).Value) & "\" _ * * * * *& Format(Cells(5, CELL.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")) * * * WB.Close * *Next CELL Next RowCount End Sub "James8309" wrote: 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 B7:AO7 to the Last row that contains data with criteria on column A 3. Close data workbook. Basically it opens up a correct file, perform a sumif then close workbook. 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? I was thinking after the "CELL.Value = Application.WorksheetFunction.SumIf(WB.Sheets("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U"))" line. 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("She et1").Range("H:U"), SUMREF, WB.Sheets("Sheet1").Range("U:U")) * * * * *MYPATH = "C:\Mydocuments\ABC\" * * * * * WB.Close * * * * Next End Sub- Hide quoted text - - Show quoted text - It still calculates sum for each cell 'one by one' and it takes too long. Is it possible to do a sumif on one cell then autofill(autocalculate) the whole row to the last row? then close workbook? i.e. if Column A has values from A6 to A100, in Cell B6, it will open up data workbook, perform a sumif then autofill from B6 to B100 then close workbook. Same process through B to AO- Hide quoted text - - Show quoted text - Thanks alot mate. You are a champ. Regards, James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill | New Users to Excel | |||
AutoFill | New Users to Excel | |||
Autofill | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |