PDA

View Full Version : Solved: Merge ranges based on condition



BrianDP1977
12-08-2005, 04:35 PM
On one sheet (?Enter Info?) I have three sections of named ranges of names (A_Names, B_Names, and C_Names) and each name within each range has a date associated with it. These dates are also clumped in respective named ranges (A_Dates, B_Dates, and C_Dates) as so:

(A_Names) (A_Dates)
Brad ......... 12 NOV 05
Tom ......... 15 DEC 05
Dave ........ 28 JAN 06

(B_Names) (B_Dates)
Chad ........ 3 MAR 06
Tim .......... 7 DEC 05
Steve ........ 13 AUG 06

(C_Names) (C_Dates)
Jill ............ 8 FEB 06
Beth ......... 22 NOV 05
Greg ......... 2 DEC 05

What I would like to have happen is that, upon comparison with today?s date (an actual updating today?s date), if a person?s associated date within any of the three original named sections is after today?s date, I want it to be stored in an array and displayed on another sheet (?Output Data?) within a named range of cells (?Output_Names?). As time progresses, the name should be removed since it no longer fills the after today?s date requirement.

I?m not sure about the whole array thing (do I need one for something like this) or can I simply have code run that will input the names matching the condition onto a sheet? An example output for today's date (8 DEC 05) would be:

(Output_Names)
Tom
Dave
Chad
Steve
Jill

BrianDP1977
12-08-2005, 06:04 PM
With much help, here's what I now have and it does the trick:


Sub Compile_Names()
Dim Nms As Range, cell As Range, r As Long
r = 31
On Error Resume Next
Sheets("Chk List").Range("Chk_List_Names").ClearContents
On Error GoTo 0
With Sheets("Extended List")
Set Nms = Union(.Range("DP6_Name"), .Range("DP7_Name") _
, .Range("CAOC_Name"))
End With
For Each cell In Nms
If cell.Offset(0, 5).Value > Date Then
Sheets("Chk List").Cells(r, 1).Value = cell.Value
r = r + 1
End If
Next cell
Sheets("Chk List").Range("A31:A" & r - 1).Name = "Chk_List_Names"
End Sub