VBA Sumif *********************** Help

[James8309]

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

 

[Madiya]

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

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("Sheet1").Range("A:J"),
SUMREF, WB.Sheets("Sheet1").Range("B:J"))
MYPATH = "C:\DOCS\DATA\"
WB.Close
Next
End Sub

Regards,
Madiya

 

[James8309]

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("Sheet1").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