×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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.

Many thanks,
D€$

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

Hi,
Divide your process into two procedures: the process preceeding filtering and the post filtering process.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

Appolication.InputBox or InputBox, with date verification, instead of breaking the code? Split code into two processes and excecute second by user after completing manual processing?

combo

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

...also there may be a better way than filtering your table and using 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

(OP)
Hi guys. I've posted the code below. Basically we have various worksheets but only one of them ("Linkage Patient ID") contains our hospital Medical Record Number (MRN) - this being shorter than the ubiquitous NHS number that appears in all worksheets - and is used in our other medical systems.

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

Sub COSD_Lung_Add_MRN_etc()
'This works for Lung, Haem, Skin, UGI & Urology
'- as these have [CancerTreatmentStartDate] in the 'Treatment' worksheet - Table19.

Dim LastRow As Long
Dim LastCol As Long
Dim WS As Worksheet
Dim Sheet As Variant
Dim Sheets_Count As Long
Dim SheetsArray() As Variant

SheetsArray = Array("Linkage Patient ID", "Linkage Diagnosis", "Demographics", "ReferralAndPatientPathway", "Diagnostic Details", "CancerCarePlan", "Staging", "Person Observations")

ActiveWorkbook.Worksheets("Linkage Patient ID").Range("C1").FormulaR1C1 = "MRN"

For Each WS In ActiveWorkbook.Worksheets

With WS
.Activate

If WS.Name <> "Imaging" And WS.Name <> "Holistic Needs Assessment" And _
WS.Name <> "CancerRecurrenceSecondary" And _
WS.Name <> "DeathDetails" And WS.Name <> "Content" And _
WS.Name <> "Linkage Patient ID" Then

    LastRow = .Range("A1").CurrentRegion.Rows.Count
    LastCol = .Range("A1").CurrentRegion.Columns.Count
    
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    
.Range("A1", .Cells(LastRow, LastCol)).Sort Key1:=Range("B1"), Header:=xlYes

'
    With .Columns("C:C")
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
    
    With .Range("C1")
    .ClearContents
    .FormulaR1C1 = "MRN"
    End With

    With .Range("C2")
    .ClearContents
    .FormulaR1C1 = "=VLOOKUP([@NHSNumber],'Linkage Patient ID'!C[-1]:C,2,0)"
    End With
    
    Range("C2").Select
        If LastRow > 2 Then ' Sometimes there's on one row of data
    Range(Selection, Selection.End(xlDown)).Select
        End If
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Else:
End If 'If WS.Name <> etc

End With 'With WS

Next WS 'For Each WS In ActiveWorkbook.Worksheets

    Sheets("Treatment").Activate ' This is where I get the 'dates' column turned in to dates.
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range( _
        "Table19[[#Headers],[CancerTreatmentStartDate]]"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
        
''****************************************************************************************************
        'Pause here and then filter 'Treatment' worksheet to required Treatment Date before proceeding

For Each Sheet In SheetsArray
    
    With ActiveWorkbook.Worksheets(Sheet)
         
   .Activate
   
     LastRow = .Range("A1").CurrentRegion.Rows.Count
     
        With .Columns("D:D")
        .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        End With
        
        With .Range("D1")
        .ClearContents
        .FormulaR1C1 = "Treatment Lookup"
        End With
    
        With .Range("D2")
        .ClearContents
       'Look up MRN based on filtered 'Treatment' worksheet
        .FormulaArray = "=VLOOKUP([@MRN],IF(SUBTOTAL(3,OFFSET(Table19[MRN],ROW(Table19[MRN])-ROW(C2),0,1)), Table19[MRN]),1,FALSE)" 'Look up MRN based on filtered 'Treatment' worksheet

        End With
        
        Range("D2").Select
                If LastRow > 2 Then
        Range(Selection, Selection.End(xlDown)).Select
                End If
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
''
    End With 'With ActiveWorkbook.Worksheets(Sheet)
Next Sheet 'For Each Sheet In SheetsArray
 
End Sub 

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

A working sample workbook (sanatized) would additionally aid in arriving at a solution.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

(OP)
Thanks Skip. Just after 5 in the UK and I've got to get the bus, but I'll get on that tomorrow.

Many thanks,
D€$

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

Are you inserting a column (C) in order to perform a VLOOKUP?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

(OP)
Hi Skip, apologies for the delay. I've attached (a link to) the test file. All the numbers have been anonymised but when I altered the 'Treatment Date' it turned them into number but it does get produced in a non-date format.

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

What sheet are we supposed to be on? When I open the workbook, it opens in Linkage Patient ID. Thought we were on Treatment?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

(OP)
Hi Skip, the code is as above and I keep that in my Personal.xls as the data are downloaded anew every month from our "Register System" as an Excel workbook with the table formats and named columns that you see and I just save it where we do. Oh, and I'm sure my users would freak every time they open an xlsm and have to decide to allow macros or not - especially as, really, the code is a one-time use in order to get it to the stage where I can distribute it - it serves no further purpose.

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

What combo and I stated at the outset was to have two processes.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

Here’s a thought. You know what date you want filtered when you start, yes?

Get your date then with an InputBox(). Why wait for Part 1 to complete?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Pausing Execution For Me To Filter A Worksheet On Treatment Date Prior To Vlookup

(OP)
Thanx guys.

Many thanks,
D€$

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close