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

[PWD]

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€$

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

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

 

[SkipVought]

...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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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'

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€$

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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€$

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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€$

 

[SkipVought]

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...
[tt]
=INDEX(ReturnRange,MATCH(LookupValue,LookupRange,0),1)
[/tt]
..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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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€$

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Thanx guys.

Many thanks,
D€$