select records where the date falls between two dates on a seperate sheet and has value of "No" in one of the cells

0

I have three worksheets in my excel workbook as shown below.

I have a worksheet called Member List

Name Due Date Amount Paid

JohnDoe18 07-Feb-18 $124.00 Yes

JohnDoe19 08-Feb-18 $321.00 No <---From this record. Update to "Yes"

JohnDoe20 09-Feb-18 $333.00 No

JohnDoe1 10-Feb-18 $521.00 No

JohnDoe2 11-Feb-18 $333.00 No

JohnDoe3 12-Feb-18 $965.00 No

JohnDoe4 13-Feb-18 $145.00 No

JohnDoe5 14-Feb-18 $225.00 Yes

JohnDoe6 15-Feb-18 $336.00 No

JohnDoe7 16-Feb-18 $365.00 No

JohnDoe8 17-Feb-18 $124.00 Yes <---except this. I don't want to copy this

JohnDoe9 18-Feb-18 $321.00 No

JohnDoe10 19-Feb-18 $333.00 No

JohnDoe11 20-Feb-18 $521.00 No

JohnDoe12 21-Feb-18 $333.00 No <---To this record

JohnDoe13 22-Feb-18 $965.00 No

JohnDoe14 23-Feb-18 $145.00 No

JohnDoe15 24-Feb-18 $225.00 No

JohnDoe16 25-Feb-18 $336.00 No

JohnDoe17 26-Feb-18 $365.00 No

JohnDoe18 27-Feb-18 $124.00 No

JohnDoe19 28-Feb-18 $321.00 No

Worksheet named "Pay Period"

FirstDate LastDate PayDay

02-Nov-17 15-Nov-17 30-Nov-17

16-Nov-17 29-Nov-17 14-Dec-17

30-Nov-17 13-Dec-17 28-Dec-17

14-Dec-17 27-Dec-17 11-Jan-18

28-Dec-17 10-Jan-18 25-Jan-18

11-Jan-18 24-Jan-18 08-Feb-18

25-Jan-18 07-Feb-18 22-Feb-18

08-Feb-18 21-Feb-18 08-Mar-18 <---Today is 09FEB18 and falls in this pay period

22-Feb-18 07-Mar-18 22-Mar-18

08-Mar-18 21-Mar-18 05-Apr-18 

22-Mar-18 04-Apr-18 19-Apr-18

05-Apr-18 18-Apr-18 03-May-18

19-Apr-18 02-May-18 17-May-18

03-May-18 16-May-18 31-May-18

17-May-18 30-May-18 14-Jun-18

31-May-18 13-Jun-18 28-Jun-18

14-Jun-18 27-Jun-18 12-Jul-18

Worksheet "Report"

Name Due Date Amount

JohnDoe19 08-Feb-18 $321.00 

JohnDoe20 09-Feb-18 $333.00 

JohnDoe1 10-Feb-18 $521.00 

JohnDoe2 11-Feb-18 $333.00 

JohnDoe3 12-Feb-18 $965.00 

JohnDoe4 13-Feb-18 $145.00 

JohnDoe6 15-Feb-18 $336.00 

JohnDoe7 16-Feb-18 $365.00 

JohnDoe8 17-Feb-18 $124.00 

JohnDoe9 18-Feb-18 $321.00 

JohnDoe10 19-Feb-18 $333.00 

JohnDoe11 20-Feb-18 $521.00 

JohnDoe12 21-Feb-18 $333.00 

I want to copy the records falling between 08FEB18 and 21FEB18 with a string "No" in the fourth column into the worksheet "Report" and update the "No" to "Yes" in "MemberList".

I am a beginner in excel and need help to write a macro for this task

Thank you very much.

Regards

Raghu

Answer
Discuss

Answers

0
Selected Answer

The attached workbook contains code to do what you want. Run the sub 'ShowReportForm'.

Observe that I assigned code names to your worksheets 'Members_List' and 'Report'. You can access the CodeName property in the worksheet properties from the VB editor. The code refers to these sheets by their code names. Changes you make to the tab names won't affect the code's action.

Note that dates in the Report Form may (must) be entered in a format understood by your computer as determined in your regional settings. The code shows a format of dd mmm yyyy. It is possible that entries by this format are rejected in which case I suggest you change the format in the code. (Function 'CheckDate' in the 'ReportForm'). The form will accept date entries like 8/2/18 or 2/8/18 and convert them to 08 Feb 2018.

The report in the attached workbook was created by my code and shows the following errors.

  1. The dates are shown in two different formats.
    The reason is that in your original Members_List some of the dates are entered as text, others as dates. Make sure to use only real dates (as in Members_List!B2) in your real data.
  2. The amounts are shown in Yen instead of $. The code copies the number format from row 2 of the Members sheet. For some reason VBA refuses to copy the custom format you have there correctly. I didn't want to hunt for the reason. You will find where the procedure 'CreateReport' sets the NumberFormat. You can apply more, less or different formats there.

As a final note: I have taken care to avoid code which would require the four or three affected columns to be consecutive. You can assign different values to their IDs in the enumeration at the top of the 'CreateReport' module. The code would read correctly from the Members sheet but when pasting to the Report cells from the lowest to the highest column would be filled and if there are blank columns in that range additional code may be required to remove them, or creation of different enumerations for the columns in the Report - small changes I trust you would be able to make yourself once you ge the hang of my code.

You may like to consider calling the procedure 'CallReportForm' using a keyboard shortcut instead of a button.

Discuss

Discussion

Please make one correction in the code contained in the attached workbook.
Sub ShowReportForm()
    ' 10 Feb 2018
    
    Dim FrmReport As ReportForm
    
    Set FrmReport = New ReportForm
    With FrmReport
        .Show vbModal
        If .Tag = "1" Then CreateReport CLng(CDate(.TbxStart.Value)), _
                                        CLng(CDate(.TbxEnd.Value))
    End With
    Unload FrmReport
    Set FrmReport = Nothing
End Sub

It should be 'If .Tag = "1"`, with the 1 in quotation marks to prevent the code from crashing if the user pressed 'Cancel'. I have made this correction in the attached solution as well.
Variatus (rep: 1218) Feb 10, '18 at 3:22 am
Add to Discussion
0

Set up Excel Advanced Data Filter.  You set up a criteria range (J1: M2) and put your criteria in the row below.  I had to move the original data that was in A1 down a few cells otherwise the data filter criteria would be hidden.  If you like you could move the criteria to another worksheet/tab. Then it's just a copy and paste to your report.

Discuss

Discussion

Hi Queue, 
I tried that. I am looking for a VBA solution.

Users want a simple solution. they just want to click a button and want to do all this in one go. 

Thanks 
Regards

Raghu
raghuprabhu (rep: 2) Feb 9, '18 at 3:53 pm
Add to Discussion
0

Hi Variatus...

Thanks very much for your effort and working on the code.

Regards

Raghu

Discuss

Discussion

Next, time please put this under the Answer that you are referencing, in the Comments/Add to Discussion section.
don (rep: 1317) Feb 12, '18 at 10:07 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login