On May 30, 5:46*am, James8309 wrote:
On May 29, 5:27*pm, Madiya wrote:

On May 29, 6:50*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

* * Reply * *Reply to author * *Forward

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Madiya * *View profile
* More options May 20, 7:52 pm

Newsgroups: microsoft.public.excel.programming
From: Madiya
Date: Tue, 20 May 2008 02:52:55 -0700 (PDT)
Local: Tues, May 20 2008 7:52 pm
Subject: VBA Sumif *********************** Help
Reply | Reply to author | Forward | Print | Individual message | Show
original | Report this message | Find messages by this author
On May 20, 3:58 am, James8309 wrote:

- Hide quoted text -
- Show quoted text -

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

* * Reply * *Reply to author * *Forward * * * Rate this post: *Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

James8309 * *View profile
* More options May 28, 9:33 am

Newsgroups: microsoft.public.excel.programming
From: James8309
Date: Tue, 27 May 2008 16:33:23 -0700 (PDT)
Local: Wed, May 28 2008 9:33 am
Subject: VBA Sumif *********************** Help
Reply | Reply to author | Forward | Print | Individual message | Show
original | Remove | Report this message | Find messages by this
author
On May 20, 7:52 pm, Madiya wrote:

- Hide quoted text -
- Show quoted text -

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

...

read more »- Hide quoted text -

- Show quoted text -

I have values from cell A6 to A? something, depending on the type, I
have different number of values to look and sum for.

so in B6, =sumif(H:U,A6,U:U). B6 will be january so this sumif will be
done out of that Jan 05.xls file.

1. Open specific excel file from correct directory ( This works
nicely ).

2. Perform sumif where criteria lies from A6 to A?.

3.Autofill for that column so it does all the sumif automatically then

4. Close the workbook

5. repeating this process for column C to AO

Thank you so much for your help

Here is your code with sumif. It will pickup sum from column "U".

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("U:U"))
Debug.Print "DD = " & CELL.Value
MYPATH = "C:\DOCS\DATA\"
WB.Close
Next
End Sub

Regards,
Madiya