Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
(OP)
Good afternoon. I have code that formats and adds various columns to a workbook produced from our waiting times database. What I do is insert a manual break so that I can filter one worksheet on treatment date 2017/18 (this is not fixed in stone) prior to coding Vlookup formulae on the other worksheets that only look for visible cells.
What I'm wondering is if there's something I can code in to pause and also allow me to access to this worksheet before proceding.
What I'm wondering is if there's something I can code in to pause and also allow me to access to this worksheet before proceding.
Many thanks,
D€$
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Divide your process into two procedures: the process preceeding filtering and the post filtering process.
Skip,
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
combo
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Plz explain in more detail and post a working example.
Also, “adds various columns”??? THAT may just be adding to the complexity of your process.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
I agree that it would work split into two procedures, but I like to see if there's a solution that I hadn't been able to find.
Oh, and I fully appreciate that it's a bit cock-eyed that in one half I use an array of worksheets and in the other has a list of <> worksheet names, but that was just how I was developing the code and I've not had time to revisit - yet - but I guess now would be as good a time as any to get on with that.
I've probably committed various sins of 'Activate' and 'Select'
CODE
I then filter each of the worksheets to exclude the "N/A" where there was no "Treatment Lookup" match on the filtered 'Treatment' worksheet.
Many thanks,
D€$
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Many thanks,
D€$
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Yes, in the first pass of code I insert a new Column "C" to get the MRN in each required sheet and in the second pass of code I insert a new Column "D" to perform a VLOOKUP on the filtered 'Treatment' worksheet.
If the most logical way is to run one procedure, filter the 'Treatment' worksheet and then run the second part as a separate procedure, then that's what I'll have to do.
Many thanks,
D€$
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Also your workbook is a .xlsx -- NO CODE!!!
So let's start over, I forgot more than I care to admit:
new workbook with code,
what sheet are we referring to?
Why are you inserting two columns so as to perform a VLOOKUP().
What's the burning question?
You really ought to learn how to use INDEX() & MAtCH(), like...
=INDEX(ReturnRange,MATCH(LookupValue,LookupRange,0),1)
..so that you can do it in your sleep!
If you use Named Ranges or Structured Tables,the formula becomes much more readable and maintainable.
Skip,
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
All our patients have an NHS (National Health Service) record number which is always 10 digits and also our own MRN (Medical Records Number) which is either 6 or 7 digits long and is the one that everyone prefers to use when accessing our various medical records systems. Unfortunately the output, as produced, only includes this MRN on one worksheets,'Linkage Patient ID' so I perform a VLOOKUP from all of the other worksheets that I am interested in analysing, to add the MRN to them. That's the end of Part 1.
Part 2.
Because of the way the "Register System" works, it tends to download records that are outside (before) the period that I'm actually interested in and I have to then filter them out - on the date of treatment in the worksheet 'Treatment'.
Part 3.
With that done I am now in a position to put in an array formula VLOOKUP in the required worksheets that will only search on the visible cells in the worksheet 'Treatment' - using the MRN.
So, the original intention of my question was to see if there's any way to pause the code at the end of Part 1, while I manually carry out Part 2, before continuing with Part 3.
(It had occurred to me that I might consider coding some prompt that would do Part 2 but I prefer to eyeball the dates to make my selection)
I've used INDEX & MATCH a few times over the years and continue to do so when "necessary".
Many thanks,
D€$
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
I remember asking a very similar question some 35 years ago before I was introduced to Event Procedures. A much olde and wiser programmer explained a new way of thinking.
Anyhow, that’s the preferred approch.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Get your date then with an InputBox(). Why wait for Part 1 to complete?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup
Many thanks,
D€$