Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumif *********************** Help
Hi everyone,
I was wondering if anyone can help with my problem. I have a workbook containing three worksheets; 'sheet1', 'sheet2' and 'sheet3'. And; 1. On 'sheet1' I have name of the months from B5 to AO5 - i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then dragged to AO5. 2. 'sheet1' Cell A1 contains the name of the workbook "ABC123" 3. From 'sheet1" cell A6 till A25. I have codes in combination of number and alphabet structure. - i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end ) 4. On my directory 'C:\Documents\Database' Under the Database folder I have many different kinds of representative name folders such as "ABC123", "DEF456", "ABB768" and so on. 5. Inside of this each name folder, It has 4 different year folders called "2005", "2006", "2007" and "2008". Inside of these year folders, I have excel files name in month as this "Jan 05", "Feb 05" all the way to "Dec 05" and so on. It is same with other year folders except the last two number digit from the name where in folder "2007", it will have excel file name "Jan 07" ~"Dec 07". 6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then do the sumif on cell A6 which contains the code "OTO35". === Here is the problem that I can't solve. What I can do with my small brain in VBA is to actually tell it to open up the file under directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it on B6. - Is there any possible way to program in VBA in such way that when msgbox pops up at the very start and I just type in the file name "ABC123" or refer to cell A1 or whatever, Open up the correct file on correct month then do a sumif automatically? i.e. A. if file name is ABC123 for this report then go to path 'C: \Documents \Database'\ABC123\ if file name is DEF456 then go to path 'C:\Documents \Database'\DEF456\ B. Then, if it is Jan-05 go to correct year folder under then open up correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on column J. D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting results cells underneath. I am pretty sure there will be alot better and efficient way of doing this. If anyone can help or give me an advice how this can be done, that would be wonderful! Thanks guys!! :D |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumif *********************** Help
On May 20, 3:58*am, James8309 wrote:
Hi everyone, I was wondering if anyone can help with my problem. I have a workbook containing three worksheets; 'sheet1', 'sheet2' and 'sheet3'. And; 1. On 'sheet1' I have name of the months from B5 to AO5 * * *- i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then dragged to AO5. 2. 'sheet1' Cell A1 contains the name of the workbook "ABC123" 3. From 'sheet1" cell A6 till A25. I have codes in combination of number and alphabet structure. * - i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end ) 4. On my directory 'C:\Documents\Database' *Under the Database folder I have many different kinds of representative name folders such as "ABC123", "DEF456", *"ABB768" and so on. 5. Inside of this each name folder, It has 4 different year folders called "2005", "2006", "2007" and "2008". *Inside of these year folders, I have excel files name in month as this "Jan 05", "Feb 05" all the way to "Dec 05" and so on. It is same with other year folders except the last two number digit from the name where in folder "2007", it will have excel file name "Jan 07" ~"Dec 07". 6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then do the sumif on cell A6 which contains the code "OTO35". === Here is the problem that I can't solve. *What I can do with my small brain in VBA is to actually tell it to open up the file under directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it on B6. - Is there any possible way to program in VBA in such way that when msgbox pops up at the very start and I just type in the file name "ABC123" or refer to cell A1 or whatever, Open up the correct file on correct month then do a sumif automatically? i.e. A. if file name is ABC123 for this report then go to path 'C: \Documents \Database'\ABC123\ * * if file name is DEF456 then go to path 'C:\Documents \Database'\DEF456\ B. Then, if it is Jan-05 go to correct year folder under then open up correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on column J. D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting results cells underneath. I am pretty sure there will be alot better and efficient way of doing this. If anyone can help or give me an advice how this can be done, that would be wonderful! Thanks guys!! :D Hi, Here is a code to get you started. Pl change the variables as required to suit you. I am no expert hance this may not be the best and most efficient code but hope it will work for you. ====== watch for line wraps <<<<<======== Sub TEST() Dim CELL As Range Dim LR As Integer Dim MYPATH As String Dim WB As Workbook Dim MYREF As String Dim SUMREF As String MYPATH = "C:\DOCS\DATA\" LR = Range("A65000").End(xlUp).Row For Each CELL In Range("B6:H" & LR) CELL.Select SUMREF = Range("A" & CELL.Row).Value CELL.Interior.ColorIndex = 33 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("A:J"), SUMREF, WB.Sheets("Sheet1").Range("B:J")) MYPATH = "C:\DOCS\DATA\" WB.Close Next End Sub Regards, Madiya |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Sumif *********************** Help
On May 20, 7:52*pm, Madiya wrote:
On May 20, 3:58*am, James8309 wrote: Hi everyone, I was wondering if anyone can help with my problem. I have a workbook containing three worksheets; 'sheet1', 'sheet2' and 'sheet3'. And; 1. On 'sheet1' I have name of the months from B5 to AO5 * * *- i.e. Jan - 05 to Apr - 08 : I simply typed Jan 05 in B5 then dragged to AO5. 2. 'sheet1' Cell A1 contains the name of the workbook "ABC123" 3. From 'sheet1" cell A6 till A25. I have codes in combination of number and alphabet structure. * - i.e. OTO35, VOF05 etc ( 3 letters and 2 number at the end ) 4. On my directory 'C:\Documents\Database' *Under the Database folder I have many different kinds of representative name folders such as "ABC123", "DEF456", *"ABB768" and so on. 5. Inside of this each name folder, It has 4 different year folders called "2005", "2006", "2007" and "2008". *Inside of these year folders, I have excel files name in month as this "Jan 05", "Feb 05" all the way to "Dec 05" and so on. It is same with other year folders except the last two number digit from the name where in folder "2007", it will have excel file name "Jan 07" ~"Dec 07". 6. On 'sheet1' cell B6 which will be underneath cell B5(Jan-05), I need to open 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' file then do the sumif on cell A6 which contains the code "OTO35". === Here is the problem that I can't solve. *What I can do with my small brain in VBA is to actually tell it to open up the file under directory 'C:\Documents\Database'\ABC123\2005\Jan 05.xls' then do sumif of A6 on Jan 05.xls Range A:J and sum data column J, then put it on B6. - Is there any possible way to program in VBA in such way that when msgbox pops up at the very start and I just type in the file name "ABC123" or refer to cell A1 or whatever, Open up the correct file on correct month then do a sumif automatically? i.e. A. if file name is ABC123 for this report then go to path 'C: \Documents \Database'\ABC123\ * * if file name is DEF456 then go to path 'C:\Documents \Database'\DEF456\ B. Then, if it is Jan-05 go to correct year folder under then open up correct xls file. i.e. 'C:\Documents\Database'\ABC123\2005\Jan 05.xls C. Do a sumif of A6 from Jan 05 file from Range A:J and sum data on column J. D. Repeat this step from Jan - 05 to Apr - 08 (B5 to AO5) then getting results cells underneath. I am pretty sure there will be alot better and efficient way of doing this. If anyone can help or give me an advice how this can be done, that would be wonderful! Thanks guys!! :D Hi, Here is a code to get you started. Pl change the variables as required to suit you. I am no expert hance this may not be the best and most efficient code but hope it will work for you. ====== *watch for line wraps <<<<<======== Sub TEST() Dim CELL As Range Dim LR As Integer Dim MYPATH As String Dim WB As Workbook Dim MYREF As String Dim SUMREF As String MYPATH = "C:\DOCS\DATA\" LR = Range("A65000").End(xlUp).Row For Each CELL In Range("B6:H" & LR) * * CELL.Select * * SUMREF = Range("A" & CELL.Row).Value * * CELL.Interior.ColorIndex = 33 * * 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("A:J"), SUMREF, WB.Sheets("Sheet1").Range("B:J")) * * MYPATH = "C:\DOCS\DATA\" WB.Close Next End Sub Regards, Madiya- Hide quoted text - - Show quoted text - Hi everyone, Coding above works beautifully opening, closing of different workbooks from specific folders then performing "sumifs" however because it does sum for each cell, when I have sum reference from A6 to A80 for an example, I have too many cells to go open workbook from particular folder, perform sumif then close workbook. How do I alter the above coding in order for me to do ; 1. Open up correct workbook (works good) 2. Perform sumif referencing A6 on cell B6. 3. Autofil column B6 to B80 (I believe this will do sumif function just like Cell B6 to B60) 4. Close the workbook 5. Repeat this process with C6 to AO6. Also, Is there anyone who knows how to solve the msgbox thingy at the top? Thanks alot. regards James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SumIf | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |