macro needs for dailly working please help

Microsoft Excel 2003 (full product)
February 22, 2010 at 08:51:28
Specs: Windows XP, PENTIMUM -4/1GB
1i have list of report id in sheet A in one file1
Report id

2i have list of report id and database used in that report id in another file2
Report id database name currentdate
File 2:
c155 alsdfj S31/01/2010
c155 aaddfj w03/01/2010
c155 aasdfj S31/01/2010
c155 alasdj S31/01/2010
c156 alaasj S31/01/2010
c156 aldddj S31/01/2010
c157 alasdj S31/01/2010

3Most of the database updates 13time per year for a report but for some report
some of the databases updates only 4times in the year.

4we have to check every day morning that for each report all the databases are
updated for specific date(31/01/2010) or not if yes then Highligh that report as green in file1.

5If any of the database is not updated check another file3 which has all the database list
with freqvency of production where if vlaue is "STD" its going to produce for each freqvency.
if we found database in file 3 with value not=STD then its ok make report as Green in file1.
but if database in file 3= std value then dont do anything in file1.
File 3:
database name freqvency of updation
alsdfj std
kdasdk std
asdkf p1/p6/p12

everyday morning we have to check eveything and then put the start date in the report
in colum c in file1.

can you help me to create macro which check the database redyness and make the report green
in file 1 for all reports whcih has not vauel assigned in colum C?

Thanks for your help in advace.

See More: macro needs for dailly working please help

Report •

February 23, 2010 at 10:30:20

I have some questions:

1. On the first sheet (sheetA), are the report ID's c155 etc. static, or are there new report ID's being created every day or at other intervals

2. In File 2 you have the same ID linked to more than one database. c155 is listed with 4 databases. Is this correct -
2a are individual reports based on more than one database and
2b are any reports based on just one database

3. What is the significance of the letter before the date.

4. In File 3 are there only two states STD and not STD, and if so can entries such as p1/p6/p12 be ignored.

5. You refer to files 1, 2 and 3. Are these different Excel workbooks File1.xls, File2.xls and File3.xls or are they worksheets within one workbook.

6. Please give the range of cells used for each item. For example - the database update frequency has database names in cells A2 to A50 and Update frequency in cells B2 to B50


Report •

February 26, 2010 at 23:56:34

Thanks a lot for your help.

below is the answer of your queries.

1.reprot id is always static it never chages and its unique too no two report has same report ids.

2.yes in file two one report is linked with more than one database
2a.yes individual report is using more than one databases
2b. yes it may possible reports may only have one database

3. No specific later before date it may be S or 4S or W anytthing but date is always at last with folowwing formate dd/mm/yy (4S31/01/10)

4. in file 4 values are STD , p1/p6/p12 , NON std etc

5. file1.xls , file 2.xls and file3.xls are excel workbooks and not the sheets.

6 database name is column C and updation freqvencies in colunm K range is not fixed as for each production file is new and number of database is more or less.

please ask me if anything is unclear.

thanks again for your suppor in advance.


Report •

February 28, 2010 at 07:16:23

I am still not clear about File 3.

In your first post you said:
if we found database in file 3 with value not=STD then its ok make report as Green in file1.

but in you last reply you said in file 4 values are STD , p1/p6/p12 , NON std etc

I presume you meant file 3, but there now appear to be at least three states:
NON std

and possibly more (etc).

Please list all possible states in file 3 and include the action required for each state.
Also is STD always uppercase and 'NON std' mixed case as shown.

In file 2 the database name is followed by current data.
You say that we have to check every day tha the databases are updated for specific date.

What is the 'specific date', how is it calculated and is it the same for all reports that day.
Can you also say what columns the data in file 2 is in.

Finally you say that the date is in dd/mm/yy format. Can you confirm that this is true for all dates, for example is 01 February 2010 01/02/10 or could it be 1/2/10


Report •

Related Solutions

March 1, 2010 at 03:47:05

First of all thanks a lot for helping me on this tedious daily task.

now regarding File 3 yes your understanding is right that there may be many state possible in file 3.
but please remember that

1. one database has only One state.
2. For the state of database we have 13 delivery of the report and for STD state database update 13time in year. if the state is p2/p4/p9 then database will only update in production P2 production P4 and production P9 for specific date for theses producution. like this for diff. database we have diff. state and the list is long for states. means it may be P3/P6/P9.

3.STD and NON will always in upper case
4. For Specific date we have calender to update database for each month for specific date i think we can take input of that date.

5.For a report using 3 database for production P2 if 2 database has STD state in file 3 then 2 databases should update for specific date. and if third database has P2/P3/P4 state then that database should update in Production P2

6. The specific date is in column F in file2.
7 yes for all the date the format is 02/03/2010 only possibility is of having character before. for example w02/03/2010 or S02/03/2010.

please feel free to ask if anything is not clear above.


Report •

March 1, 2010 at 05:54:46

From what you have said so far, this is what I think the logic is:
1. Select a Report ID on Sheet A in File1.xls

2. Search Sheet1, column A in File2.xls for the Report ID.
a. I have used Sheet1 as no other sheet name has been mentioned.
b. I have assumed that Report ID is in column A.

3. Find each matching Report ID in column A and retrieve each database name from column B, on the same row.

4. For each database name:
a. Extract the 'Current Date' from column C , on the same row - (extract the right 8 characters only dd/mm/yy)
Note: you stated that the date part was dd/mm/yy (Response #2), but in Response #4 you say yes for all the date the format is 02/03/2010 . Please confirm - is the date format dd/mm/yy or is it dd/mm/yyyy.
b. Compare the 'Current date' to the 'Specific date' in column F.
Note: Need to confirm the format of the dates in column F
c. If the 'Current date' is the same as the 'Specific date', then flag this database as 'green'
d. If all databases linked to the initial Report ID are 'green' then color the Report ID green.
e. If any database linked to the initial Report ID is not flagged 'green' i.e. Current date <> Specific date, then:
f. Look up the database name in column C, Sheet1 in File3.xls and retrieve the 'frequency of update' information from column K, on the same row.
c. If the frequency text is not "STD" then if all databases linked to the initial Report ID are either flagged 'green' or the database frequency of update is not STD, color the Report ID green

Other issues:
A. In the initial post you say put the start date in the report in column C in file1
How is this start date created - is it in any way related to the proposed macro, because you go on to say make the report green in file 1 for all reports whcih has not vauel assigned in colum C
B. In Response #4 you give additional information on the database states (File3.xls, Sheet1, Column K). I have read this but so far all I can see is that If the value is not "STD" then the database is OK and the relevant report can be colored green. For all other states the relevant report will not be colored green, unless the 'Current Date' is the same as the 'Specific Date'.

You need to go through this and confirm that the logic is both correct and complete.
Without correct and complete logic, I can't consider trying to write code or create formulas to perform the task required.

As you can see this is not a simple task, and I am concerned that the data provided is not complete. I don't want to write something only to find that there is additional data that is part of the process.


Report •

March 2, 2010 at 08:34:30

Thanks for answer.

till point 3 everything is fine.

please see below for point 4 onwords.

1.point 4a is also fine format is dd/mm/yy
3.point 4b is not correct you will get current date in column C but specific date is not present in file2 column F we have to take input in the begining from user only.
4.point 4c is correct
5.point 4d is correct
6.point 4e is correct
7.point 4f is correct
8. point c after point f is also correct.

Other issues
A. yes the start date in column C has relation with this macro
because after making report green if we can add the start date in column C (May be through macro or manually for green reports) then for next day if column C has value
we will not check anything as report is already checked for database readiness.

so if we can put value(today's date 03/02/10) in column C after making report ID green it will be great or i will put it manually after looking for green report ids. and second day(04/03/10) we will not look for those report ids for database check.

B. this is not the case because
for example currently i am in production P2
and if the database state is p2/p4/p6 then this database should update surly in P2 as like other database so we can not make report id green till this database also get current date = specific date.

but it is too complex then you can make logic if databae state is Non "STD" then database is ok. but make that report id as RED in color so we can at least check it manually. out of 500 reports we have few reports around 40 like this. because this is some thing we have to look manually only i guess.

thanks again for your support

please feel free to ask if anything is not clear.

Report •

March 7, 2010 at 10:58:11

please put your comments.


Report •

March 11, 2010 at 06:33:15

Although I am asking further questions, I feel that due to the number of variables and the number of possible tests (comparisons) required in a VBA macro to do what you want, you would do better to employ someone locally who can actually see the process in action, and can produce a flow chart, working with the people doing the work. As I hinted at in Response number 5, I was concerned that not all the necessary information was available, and I still feel that the process description is incomplete.


Anyway here are the questions:

I have been through your response and I have added or modified some steps. Please confirm if the following statements (1-3) are correct:

1. As a first step, the user must input a date - referred to as the 'Specific date'
2. If File1.xls, column C contains a date, then the database in column A on the same row does not need to be checked.
3. If a database name in File1.xls, column A is flagged green (having met all the required criteria) the user-entered 'Specific date' is to be entered in column C on the same row.

A. What is the Specific date - please provide a definition, including it's relationship to the date on which it is input and it's relationship to production periods.

B. Do any of your files contain tables showing the relationship of production periods such as P2 to actual dates.

C. Regarding your point 17,
You say for example currently i am in production P2 and if the database state is p2/p4/p6 then this database should update surly in P2 as like other database so we can not make report id green till this database also get current date = specific date.

Unfortunately I cannot follow what this means.
C1. How do I know what 'production' you are in. In your previous posts you did not say that the database tests were linked to a 'production'.
C2. The database frequency (which may for example contain P2) is in File3.xls, column K and the database current date is in File2.xls, column C.
The database current date can be checked against the user inputted Specific date, but how does the production period come into this equation.
C3. When testing Current dates against Specific date, is this test only that 'Current date = Specific date' or is it that 'Current date is later than or equal to the User entered Specific date'.
C4. Can you clarify what this is if the database state is p2/p4/p6 then this database should update surly in P2 as like other database. There is probably a typo in here as well (surly).


Report •

March 12, 2010 at 22:18:52

thanks for your mail.

1. all the 3 steps you have ask is ok for me


pleae also note that the dates present in file2.xls can not be later after specif date it can only be before or same as specific date.

As you suggested in last reply i think i can give simple steps to exaplin process again.

1. open file1.xls
1. In file 1.xls for each report id if in column c check date is already there if cell is empty then only we have to go for check otherwise stop there only.
2. if in file 1. xls in column c is blank ask from user a specific date required to compaire with file2. xls
we can enter the specific date in any cell of file1.xls so only we need to take input from that cell only. no user interfearnce requried.

3 After taking specifc date , open file 2 where we have multiple line with diff. datatabsa name for one report id. for each line we have to compare date present in column K
id if its match with specific date for all database then in file 1. make that report id as green.
4. If for one or for more database in file2.xls date is less then the specific date then go to file3.xls if the freqvnecy in file3.xls for that database is "STD" then leave it we have to wait till datatbase gets ready. if its not "STD" then in file 1.xls make that report id as RED we will check it mannually as reports with this issues are less in number.

i hope above simple steps is more logical makes my requirement celar to you.

please dont confuse with production number and dates produciton number only can idetified by specifc date.

feel free to ask for any clarification.

thanks again for your time and intrest to resolve this issue.


Report •

March 16, 2010 at 09:51:39

please someone help to create at least simple macro for above logic.

Report •

March 17, 2010 at 12:45:48

I have created a macro -
Please note that the issue here is not whether the macro is 'simple' or not, it is whether the logic is correct.
Writing the macro is the easy part.

Here are some notes about the macro:

1. As you have not provided any information on paths to files, this macro requires that all three files are opened first before running the macro. You can add code at the start to open file2.xls and file3.xls if you wish, once everything else works.

2. To find the range of cells holding the data, I use the .End (xlUp) technique. For this to work, all cells below the data in the tested column must be empty.

3. The end of data on file3.xls, Sheet1 uses column C as there is no information as to the contents of column A. For file1.xls and file2.xls, column A has been used.

3. I do not know how many header rows there are above the data in each file. I have assumed that all data starts on row 2 in each file. Change the start address rows if necessary.

4. All file names are as posted - all lower case:

5. The worksheets are named as follows (only one is used in each workbook):
file1.xls is SheetA
file2.xls is Sheet1
file3.xls is Sheet1

6. When asking user for 'Specific' date, if it is not entered in dd/mm/yyyy format the macro terminates. Additional code could be added to provide feedback and allow the user to re-try

7. If there are no matching ID's in file2.xls (compared to file1.xls) the macro terminates

8. As the example data supplied is very limited, and as I have no idea of the relationship of database 'dates' such as w03/01/2010 to the 'Specific' date, I cannot test this macro properly.

9. I suggest you make some copies of the three files and load this macro into file1.xls (it won't work if loaded into either of the other files). As I presume you know, the changes made by macros cannot be undone with the Undo button, so test this only on copies of your files.

10. Start by single stepping the macro and record the line at which it stops or does not work as expected, and provide me with information on what happened - which line, and the error message, as well as the data relevant to the point at which it stopped.

11. I have not included any error handling code, as it is appropriate for the VB code to show errors at this stage.

12. If the code completes, but the result is not 'as expected' provide the outcome for the first ID that is not 'as expected', and a list of all the data that belongs to that ID - and which file and which cell the data is in, as well as what the expected outcome was.

13. If it works, remember me in your Will

Here is the 'simple' macro, (with plenty of comments):

Option Explicit

Sub DBflags()
Dim strDBArray(10, 1) As String
'strDBArray(x, 0) holds DB name
'strDBArray(x, 0) holds DB status (white, red or green)
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngDBListStart As Range
Dim rngDBListEnd As Range
Dim rngDBStatusStart As Range
Dim rngDBStatusEnd As Range
Dim rngCellF1 As Range
Dim rngCellF2 As Range
Dim rngCellF3 As Range
Dim strSpecDt As String
Dim dtSpecDt As Date
Dim strExtndDt As String
Dim dtThisDate As Date
Dim strID As String
Dim intDBCount As Integer
Dim blnRed As Boolean
Dim n As Integer

'set start and end of each range
'All Files must be open

'set start for file1.xls
Set rngSrcStart = Workbooks("file1.xls").Worksheets("SheetA").Range("A2")

'find end for file1.xls
Set rngSrcEnd = Workbooks("file1.xls").Worksheets("SheetA"). _
                Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'set start for file2.xls
Set rngDBListStart = Workbooks("file2.xls").Worksheets("Sheet1").Range("A2")

'find end for file2.xls
Set rngDBListEnd = Workbooks("file2.xls").Worksheets("Sheet1"). _
                Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set start for file3.xls (use column C)
Set rngDBStatusStart = Workbooks("file3.xls").Worksheets("Sheet1").Range("C2")

'find end for file3.xls (use column C)
Set rngDBStatusEnd = Workbooks("file3.xls").Worksheets("Sheet1"). _
                Range("C" & CStr(Application.Rows.Count)).End(xlUp)
'get 'Specific date' from user
strSpecDt = InputBox("Enter Specific Date in this format: dd/mm/yyyy" & _
            vbCrLf & "Include / between elements, no spaces allowed", _
            "Specific Date")

'test strSpecDt - quit if not correct
If Len(strSpecDt) <> 10 Or Not IsDate(strSpecDt) Then Exit Sub

'convert input string to Excel date
dtSpecDt = DateSerial( _
            Right(strSpecDt, 4), _
            Mid(strSpecDt, 4, 2), _
            Right(strSpecDt, 2))
'Loop through each ID in column A of SheetA  in file1.xls
For Each rngCellF1 In Range(rngSrcStart, rngSrcEnd)
    'test if there is no date in column C
    If rngCellF1.Offset(0, 2).Text = "" Then
        'save ID
        strID = rngCellF1.Text
        'no date, so get all matching databases
        'set counter for matching ID databases
        intDBCount = 0
        For Each rngCellF2 In Workbooks("file2.xls").Worksheets("Sheet1") _
                                .Range(rngDBListStart, rngDBListEnd)
            If rngCellF2.Text = strID Then
                'save database name
                strDBArray(intDBCount, 0) = rngCellF2.Offset(0, 1).Text
                'If date in column C is => than 'Specific' date flag DB 'green'
                'convert text to date
                'get date part - remove any prefix characters
                strExtndDt = Right(rngCellF2.Offset(0, 2).Text, 10)
                'convert text string to Excel date
                dtThisDate = DateSerial( _
                                Right(strExtndDt, 4), _
                                Mid(strExtndDt, 4, 2), _
                                Left(strExtndDt, 2))
                If dtThisDate >= dtSpecDt Then
                    strDBArray(intDBCount, 1) = "green"
                    strDBArray(intDBCount, 1) = ""
                End If
                'increment database array counter
                intDBCount = intDBCount + 1
            End If
        Next rngCellF2
    End If
    'if no matching ID's in file2.xls - quit
    If intDBCount = 0 Then Exit Sub
    'compare date for each database
    For n = 0 To intDBCount - 1
        If strDBArray(n, 1) <> "green" Then
            'Test if DB is labelled 'STD'
            For Each rngCellF3 In Workbooks("file3.xls").Worksheets("Sheet1") _
                                        .Range(rngDBStatusStart, rngDBStatusEnd)
                'find match for DB
                If rngCellF3.Text = strDBArray(n, 0) Then
                    'test database type from column K (offset = 8 from column C)
                    If rngCellF3.Offset(0, 8) = "STD" Then
                        'flag database 'red'
                        strDBArray(n, 1) = "red"
                        'flag DB 'red'
                        strDBArray(n, 1) = "green"
                    End If
                End If
            Next rngCellF3
        End If
    Next n
    'test database flags in array for 'this' ID
    'if any flag is red, then result is red
    blnRed = False
    For n = 0 To intDBCount - 1
        If strDBArray(n, 1) = "red" Then
            blnRed = True
        End If
    Next n
    'set cell color
    If blnRed = True Then
        rngCellF1.Interior.Color = vbRed
        rngCellF1.Interior.Color = vbGreen
    End If
    'place 'Specific' date in column C
    rngCellF1.Offset(0, 2).Value = dtSpecDt
Next rngCellF1
End Sub

Don't forget to include the 'Option Explicit' statement before the subroutine name. It will reduce the risk of errors when changes are made.


Report •

Ask Question