Report

Need help with Macro to generate Monthly Overview [Solved]

Ask a question ZhengPeng 5Posts Monday February 6, 2017Registration date February 14, 2017 Last seen - Last answered on Feb 27, 2017 at 10:57 PM by ZhengPeng
Hi all!

I have been trying to build a Macro that allows me to generate a new worksheet with the information from a Master Sheet where i will key in the data. It has to do with attendance taking and I have to generate an overview at the end of the month. However I am struggling with the multiple dates component of the macro as well as due to the number of names I am required to keep track of (about 100)

What I key in in the Master Sheet will be two columns Name and Date. At the end of the month, i am required to generate an overview. Lets call this overview Sheet2. For Sheet2, in the Y axis column, there will be a namelist which has been pre-entered and the X column I will need the dates.

Take for example, for a Person A, if he was present on the 1st and the 3rd of Jan, I would key in into the Master Sheet as Person A, 1st Jan and another row as Person A, 3rd Jan as shown below:



The overview will require the intersection between Person A name and the date 1st and 3rd to be marked with a 1 and the box to be filled with yellow color and a blank for 2nd Jan due to him being absent. An example is shown below:



So if a person in the Master Name List has been absent for the entire month, he should turn up on the Sheet2 without any "1" indicated.

Any suggestions on how the code should be like? Much appreciated for any advice!

Sample file:
See more 
Helpful
+0
plus moins
Hi ZhengPeng,

My suggestion would be to get rid of the Master sheet and to use Sheet2 to input data. Type a 1 on the days the person worked and let conditonal format colour the cell yellow. That seems faster then inputting the data in the Master sheet.

Best regards,
Trowa
ZhengPeng 5Posts Monday February 6, 2017Registration date February 14, 2017 Last seen - Feb 7, 2017 at 07:05 PM
Dear TrowaD,

Thanks for the reply!

Unfortunately, the master-sheet is a requirement to fill in with other information which i have omitted here as they are not relevant to the overview. Hence, if the Overview can be generated automatically, it would be more convenient for me as I don't have to do double work.

I have tried to use the formula IF(AND) for the two sheets to compare and place a 1 when the date and name is matched. However this formula only appears to always stop at one selection, which is to say if Person A works on 1st and 3rd, the excel formula will only mark "1" on the intersection of Person A and 1st and not on 3rd.

Any advice on how to I get around this restriction? I have thought of using INDEX but cant seem to make a formula which works for what I want to do.

Any advice is much appreciated.
Reply
Leave a comment
Helpful
+0
plus moins
Hi ZhengPeng,

OK, then try and see if the code below can help you out.
To implement the code, right-click your Master sheets tab, select View Code and paste the code below in the big white field.

Now confirm an entry in the Master sheet column B (I assumed column A for Persons and column B for dates) and check what happened to sheet2. It should place a 1 in the right place as well as color the cell yellow. Any new entry will be automically processed this way. Hope you like.


Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range
Dim MyCol, MyRow As Integer

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

Set mFind = Sheets("Sheet2").Rows(1).Find(Target.Value)
MyCol = mFind.Column

Set mFind = Sheets("Sheet2").Columns("A:A").Find(Target.Offset(0, -1).Value)
mycell = mFind.Row

With Sheets("Sheet2").Cells(mycell, MyCol)
    .Value = 1
    .Interior.Color = 65535
End With
End Sub


Best regards,
Trowa
ZhengPeng 5Posts Monday February 6, 2017Registration date February 14, 2017 Last seen - Feb 14, 2017 at 12:08 AM
Hi TrowaD,

Thanks for the suggestion. However after trying it, Excel returns with a Run-time error "91" Object variable or With Block Variable not set and the error occurs because of this line in the code
MyCol = mFind.Column

Any ideas how to solve this?
Reply
Leave a comment
Helpful
+0
plus moins
Hi ZhengPeng,

That would mean that the date you entered in column B of the Master sheet isn't found in the first row of Sheet2.

When they do match, then consider uploading your workbook (careful with sensitive data) to a free filesharing site like www.speedyshare.com or ge.tt and then post back the download link, so I can take a look.

Here is the code which shows you a message box instead of the error message in case either the data or the person can't be found on sheet2:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range
Dim MyCol, MyRow As Integer

If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

Set mFind = Sheets("Sheet2").Rows(1).Find(Target.Value)
If mFind Is Nothing Then
    MsgBox "The date entered can't be found on Sheet2. Data not processed."
    Exit Sub
End If
MyCol = mFind.Column

Set mFind = Sheets("Sheet2").Columns("A:A").Find(Target.Offset(0, -1).Value)
If mFind Is Nothing Then
    MsgBox "The Person corresponding to the date entered can't be found on Sheet2. Data not processed."
    Exit Sub
End If
MyRow = mFind.Row

With Sheets("Sheet2").Cells(MyRow, MyCol)
    .Value = 1
    .Interior.Color = 65535
End With
End Sub


Best regards,
Trowa
ZhengPeng 5Posts Monday February 6, 2017Registration date February 14, 2017 Last seen - Feb 14, 2017 at 10:48 PM
Hi TrowaD,

Many thanks for the code. Much appreciated. Code works great!

The dates only match if i key it into excel in the same format as with the Sheet2 but I think this should be a minor problem.

Additionally, is it possible for the Msgbox to be replaced by filling in the box with red should the name or date not match?
Reply
ZhengPeng 5Posts Monday February 6, 2017Registration date February 14, 2017 Last seen - Feb 14, 2017 at 10:55 PM
Additionally I would like this code to be a button to be pressed. I have tried inputting to a VBA button but it doesnt appear in the macro page. Curious to know why!
Reply
Leave a comment
Helpful
+0
plus moins
Hi ZhengPeng,

Code has been adjusted.

Msgbox has been replaced by the use of red cells. Either the date, person or both will become red cells when no match is found.

Code can be used in combination with a button. My advise is to put the code in a standard module and use the code line "Call RunMe" for the Button code. This way you keep your codes in one place (modules) instead of spread across multiple sheets.

As to why, you probably see the difference in code. The first code had a Target range to refer to something that has changed. Now that we don't use the Worksheet_Change event anymore, we have to replace the Target variable.

Here is the code:
Sub RunMe()
Dim mFind As Range
Dim MyCol, MyRow As Integer

For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Rows(1).Find(cell.Value)
    If mFind Is Nothing Then
        Cells(cell.Row, "B").Interior.Color = 255
        MyCol = vbNullString
    Else
        MyCol = mFind.Column
    End If
    
    Set mFind = Sheets("Sheet2").Columns("A").Find(cell.Offset(0, -1).Value)
    If mFind Is Nothing Then
        Cells(cell.Row, "A").Interior.Color = 255
        Exit Sub
    End If
    MyRow = mFind.Row
    
    If MyCol <> vbNullString Then
        With Sheets("Sheet2").Cells(MyRow, MyCol)
            .Value = 1
            .Interior.Color = 65535
        End With
    End If
Next cell
End Sub

Best regards,
Trowa
Zheng Peng- Feb 21, 2017 at 10:58 PM
Hi!

Many thanks for the help! Much appreciated!
Reply
Zheng Peng- Feb 21, 2017 at 11:07 PM
Tried the code and ended with the "Subscript out of range" error.

Cant seem to find the problem!
Reply
TrowaD 2259Posts Sunday September 12, 2010Registration date ModeratorStatus August 15, 2017 Last seen - Feb 23, 2017 at 11:18 AM
Hi ZhengPeng,

My first thought would be that you don't have a sheet named Sheet2.

Can you let me know which code line turned yellow

Best regards,
Trowa
Reply
ZhengPeng- Feb 27, 2017 at 10:57 PM
Oh I see! Thanks for the input!

Code works great now!

Thank you for your tremendous help!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!