• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date search and display report worksheet

T

tmc.planning

New Member
I am looking to code vba macro to search table with dates in column C (the date format is DD-MMM). this will search and place in row list on another sheet for upcoming inspections for week 1. There will be another row for week 2 and week 3.


When sorted for dates this will copy the date in cells in date order starting from today being day 1 till day 7 (this will equal week 1), and so on for week 2 and week 3.


Along with the date will have column A and it primary name to also paste along side the date applicable to each date.


There is 2 data tables on seperate sheets which will need review both these to the same problem.


Being very new to coding and books / internet I have found myself confused.


I think I can use case 1, case 2, case 3 for each worksheet


Can someone please assist with guidance where to begin?
 
Hi, tmc.planning!


I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community works.

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


Regards!
 
Hi, tmc.planning!


No problem at all, and nothing to apologize for. Just tried to guide you thru the natural and easiest way to come in.

If you want to upload a sample file, it will aid a lot to people who might be helping you.

Could you do it now so as i can give a look to it?


Regards!
 
SirJB7,


Can I attach a excel page to this thread as maybe easier to view and not sure if can explain it clear enough


Sample file has columns A (label) and C (Date), columns D & E is date column plus 10 days and 20 days respectively.


On adjacent work sheet for true value date week 1 with today being day 1 would put the date in cell with label in adjacent cell. This would also be true for dates in clumns D and E with the date + 10 and date +20.


This would also need look at row down for week 2 (days 8 - 14) and then week 3 (days 15 -21).


Label Date Date+10 date+20


A 29-Mar 8-Apr 18-Apr

B 31-Mar 10-Apr 20-Apr

C 26-Mar 5-Apr 15-Apr

Day 1 Day 2 --- Day 7


Date Label Date Label Date Label


Week 1

PSC

ADD
 
Hi, tmc.planning!

In one of the three green sticky topics at this forums main page you have the instructions for uploading files.

Here's the link:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!
 
SirJB7,


have uploaded file page to speedy share


http://speedy.sh/8Jgje/Test-Workbook.xls


This has 2 sheets of table data with 3rd sheet to have report of dates / labels to rows


cheers
 
Hi, tmc.planning!


Could you please fill manually a couple of rows of sheet "PSC Charts" so as to define the exact placement of the data? There are many rows and I don't realize where do you want it. Please do it with both tables. Thanks.


Regards!
 
Have updated the "PSC Charts" with dates and label data.


Realised that would need 2 rows for "ADD" as they have possible 2 lines on some dates.


The "PSC Tables" have many more lines but have put these as examples.


I have control page that will enter today date thus on "PSC Charts" Day 1 = today


http://speedy.sh/TRGqC/Test-Workbook.xlsm


Regards
 
Hi, tmc.planning!


Very clear your examples. Let's see if this file does the job:

http://dl.dropbox.com/u/60558749/Date%20search%20and%20display%20report%20worksheet%20-%20Test%20Workbook%20%28for%20tmc.planning%20at%20chandoo.org%29.xlsm


I added a new sheet "PCS Charts Alternative" that is equivalent to old "PCS Charts" with a slight modification in the design. I think is more easy to read. You decide. There are two macros (BuildCalendar and BuildCalendar_Alt -each one calls another one with same name and _R1 as suffix) that fill respectively the original chart and the suggested chart.


For both, I created many named ranges so as to make easier referencing them from within VBA.


Just advise if anything's hard to understand.


Regards!
 
Ok, have placed the code in and changed for the actual sheet names and fails on 1st line code of build calender.


The sheet names I tried to rename to PSC Table 1 and PSC Table 2 and still errors.


I believe I have constants and declarations, do you need have spaces or under score separators for sheet names? would this also be in the declarations for range?


Ihave taken all the spaces out of sheet name and fails again on 1st line.


I do appreciate your alternate chart as that is much simpler design. Some times doing this too long I can't see the forest for the trees.


my personal version of office is 2007 but my work version is 2003 if this would make difference? I am trying to run this with my laptop now which is 2007.


regards
 
Thought of new issue that the data in labels had "-" in the name which thought could have been error. Took this hyphen out and still the error in code


it breaks in first line code


Set rngPSCTable1 = Worksheets(gksSheetPSCTable1).Range(gksPSCTable1)


Am back into office in morning and will try run again see if can get it running


Regards
 
Hi, tmc.planning!


I think there's a problem with named ranges. As I wrote you, I defined many named ranges to easily handle range references. So if you're inserting the code in your real workbook, you have to create them as well.


They are:

PSCTable1, refers to 'PSC Table 1'!$A:$E, scope workbook

PSCTable2, refers to 'PSC Table 2'!$A:$E, scope workbook

TodayDate, refers to 'PSC Charts'!$E$2, scope worksheet

W1T1, refers to 'PSC Charts'!$E$9:$R$11, scope worksheet

W1T2, refers to 'PSC Charts'!$E$14:$R$16, scope worksheet

W2T1, refers to 'PSC Charts'!$E$20:$R$22, scope worksheet

W2T2, refers to 'PSC Charts'!$E$25:$R$27, scope worksheet

TodayDate, refers to 'PSC Charts Alternative'!$E$2, scope worksheet

W1T1, refers to 'PSC Charts Alternative'!$E$9:$K$11, scope worksheet

W1T2, refers to 'PSC Charts Alternative'!$E$14:$K$16, scope worksheet

W2T1, refers to 'PSC Charts Alternative'!$E$22:$R$24, scope worksheet

W2T2, refers to 'PSC Charts Alternative'!$E$27:$R$29, scope worksheet


Each group of 5 with worksheet scope corresponds to each model of output chart, if you choose only one of them you don't need to define both groups and you can change the scope to workbook which is the default. Accordingly you'll need only 2 of the 4 subroutines that are in the code.


BTW, under the label 'constants' at the beginning of the module and at the beginning of the 2 main procedures, you'll find variables where you can change:

... the worksheets names

Const gksSheetPSCTable1 = "PSC Table 1"

Const gksSheetPSCTable2 = "PSC Table 2"

Const ksSheetPSCCharts = "PSC Charts" (+ the alternative)

... the named ranges

Const gksPSCTable2 = "PSCTable2"

Const gksPSCTable1 = "PSCTable1"

Const gksTodayDate = "TodayDate" (+ the alternative)

Const gksW1T1 = "W1T1" (+ the alternative)

Const gksW1T2 = "W1T2" (+ the alternative)

Const gksW2T1 = "W2T1" (+ the alternative)

Const gksW2T2 = "W2T2" (+ the alternative)

... just in case you want to use another names.


Regards!
 
SirJB7,


The constants / declarations is all new to me so have plenty home work.


I understand how you have defined the range to the above tables with the cell references


where do I insert this data? This is where you lost me


For the constants I do understand the names and spaces and yes this is what I tried but as found the range information must be defined.


This is certainly giving me a very fast lesson in my coding which strange for the stress and frustration I do enjoy, especially if can save my team time.


You guys are stars and much appreciate your help.


regards
 
Hi, tmc.planning!


Open both files, mine and yours. Go to mine, and write down all the named ranges definitions. Go to yours and add them. The names are inserted from the Formula tab, Names Admin. Once you did that for the 12 names, you press Alt-F11 and go to the Visual Basic Editor.

In the left pane, under the "VBA Project (the file name)", you'll find many MS Excel objects (one for each sheet and one for This Workbook) and modules with one module.

Right click, export file.

Then close my file, go back to your file, Alt-F11, File, Import and select the previously exported file.

That's all.

Regards!

PS: As I told you earlier, this isn't for you right now, I'd recommend you to keep the manual input version.
 
SirJB7,


Your instructions of the naming definitions was excellent and had me in the right direction immediately. After some trial and error for the names of the ranges I have been able to have this working. It is just on home laptop but will run in office tomorrow and give full test.


I was able to rename the sheets, and also brave enough to add another week. This gives the full operation projection as required.


Will send update following tomorrow test just ensure working on company drives.


much appreciate as you have saved me and my team approximately 2 to 3 hours work each day. This should get the boss off my back for few more days as he will not know how this has been achieved.


cheers
 
Hi, tmc.planning!

Glad it solved your issue. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top