Hi all,
Feel like I'm really close to solving this, but I've gotten stuck trying to reference a named range in another workbook.
My code is used to select random rows from a report using input box criteria in several columns (date range, line of business, number of records to return, etc). I've also explicitly filtered results by certain keywords in another column "Approved".
I've got a list of 40 or 50 codes (most are 2 or 3 letter / number combinations) in a named range "AllBands" in another worksheet, but I'm not able to figure out how to reference the range to "allow" any of the codes in that list. The list could change over time, so I've used a dynamic named range. Also, I didn't want to explicitly script "CC" or "CRT" or "WY9" or etc. There's just too many of them...
Here's the first portion of my code:
Sub GenerateAuditSample()
Static StartDate As String, LOBName As String, BandName As String, Status As String
Static EndDate As String
Dim sDate As Date
Dim eDate As Date
Dim Data, Possible, This
Dim i As Long, j As Long
Dim Ws As Worksheet
Static Amount As Long
Dim SheetName As String
[COLOR=#FF0000]'Don't think this one is correct?
Dim Bands As Variant[/COLOR]
'Get the start date of the range you need
Do
StartDate = InputBox("Enter START date (Format as MM/DD/YYYY)", "Generate Random Sample", StartDate)
If StartDate = "" Then Exit Sub
If Not IsDate(StartDate) Then Beep
Loop Until IsDate(StartDate)
'Get the END date of the range you need
Do
EndDate = InputBox("Enter END date (Format as MM/DD/YYYY)", "Generate Random Sample", EndDate)
If EndDate = "" Then Exit Sub
If Not IsDate(EndDate) Then Beep
Loop Until IsDate(EndDate)
sDate = StartDate
eDate = EndDate
[COLOR=#FF0000]'Can I reference a named range in another workbook, eg. "AnotherFile.xlsx"
Set Bands = Range("AllBands")
[/COLOR]
LOBName = InputBox("Enter LOB you want sampled (ERD, CAR, LAB, MED,etc)", "Generate Random Sample", LOBName)
If LOBName = "" Then Exit Sub
If Amount = 0 Then
Amount = 5 'Default
Else
Amount = Amount + 1 'Adjust from last call (see code below)
End If
Amount = Application.InputBox("Enter amount (Total number of rows / records you want to return - up to the total number available for date and name parameter)", "Generate Random Sample", Amount, Type:=1)
If Amount <= 0 Then Exit Sub
'Read in all data
Data = Sheets("Master").Range("A1").CurrentRegion.Value
'Initialize
Amount = Amount - 1
Possible = Array()
j = -1
[COLOR=#FF0000]'THE LAST CRITERIA CHECKER IS WHAT'S CAUSING THE PROBLEM - (DATA(i,2) = ... [/COLOR]
For i = 2 To UBound(Data)
'Checks in Column 9 for date, checks in column 6 for the LOB that you enter
If (Data(i, 3) >= sDate) And (Data(i, 3) <= eDate + 1) And (Data(i, 4) = LOBName And _
(Data(i, 5) = "Approved") And[COLOR=#FF0000] (Data(i, 2) = Bands)) _[/COLOR]
Then
j = j + 1
ReDim Preserve Possible(0 To j)
Possible(j) = i
End If
Next
'Found any?
If j < 0 Then
MsgBox "No match found for " & sDate & " - " & LOBName, vbExclamation, "Generate Random Sample"
Exit Sub
End If
'More than 5?
If j > Amount Then
'Get 5 random rows of the possible rows
Randomize
ReDim This(0 To Amount)
For i = 0 To Amount
This(i) = Possible(RandomUnique(0, j, i = 0))
Next
Else
'Just this
This = Possible
End If
'Copy the rows to the top
For i = 0 To UBound(This)
For j = 1 To UBound(Data, 2)
Data(i + 2, j) = Data(This(i), j)
Next
Next
'Output
SheetName = NewSheetName(LOBName & " " & Format(sDate, "mm/dd/yyyy"))
Set Ws = Worksheets.Add(After:=Sheets(Sheets.Count))
Ws.Range("A1").Resize(UBound(This) + 2, UBound(Data, 2)).Value = Data
Ws.Name = SheetName
End Sub
Display More