Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a filtered range and copy unique values to a second sheet
Hi again,
Accidentally hit the Send Now button before I had the question written. Sorry about that. I've got two sheets with values in columns E and A, respectively. I need to loop through column E on the first sheet (wksQB), and have it check column A on the second sheet (wksMDR), and if there's a match, press on to the next cell in the filtered range. If there isn't a match, then the macro is to copy the value on the first sheet to the next open cell in column A on the second sheet. Below is what I've got. It compiles ok, but it's erroring out in a couple spots. Not sure what I'm doing wrong here. I know I can go a longer route and do the cell activate copy find, etc. etc., but I'm trying to keep this code a bit leaner and cleaner. Any help would be apprecieated. Thanks. Frank Sub Macro5() ' Dim nameRange As Range Dim wksQB As Worksheet Dim wksMDR As Worksheet Dim var As Variant Dim cell As Range Set wksQB = ActiveWorkbook.Sheets("QueryBuster") Set wksMDR = ActiveWorkbook.Sheets("MDR Worksheet") Set nameRange = wksQB.Range("E2", Range("A65536").End(xlUp)).SpecialCells(xlCellType Visible) wksQB.Range(nameRange).Select For Each cell In nameRange var = Application.Match(cell, wksMDR.Columns(1), 0) If Not IsError(var) Then GoTo NextVar ActiveCell.Value = wksMDR.Range("A2", Range("A65536").End(xlUp).Offset(1, 0)).Value NextVar: Next cell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a filtered range and copy unique values to a second sheet
Hi Frank,
Am Fri, 03 Jun 2016 20:04:23 -0400 schrieb Phrank: I've got two sheets with values in columns E and A, respectively. I need to loop through column E on the first sheet (wksQB), and have it check column A on the second sheet (wksMDR), and if there's a match, press on to the next cell in the filtered range. If there isn't a match, then the macro is to copy the value on the first sheet to the next open cell in column A on the second sheet. Below is what I've got. It compiles ok, but it's erroring out in a couple spots. Not sure what I'm doing wrong here. I know I can go a longer route and do the cell activate copy find, etc. etc., but I'm trying to keep this code a bit leaner and cleaner. Any help would be apprecieated. try: Sub Macro5() ' Dim wksQB As Worksheet, wksMDR As Worksheet Dim rngC As Range, nameRange As Range Dim LRow As Long Set wksQB = ActiveWorkbook.Sheets("QueryBuster") Set wksMDR = ActiveWorkbook.Sheets("MDR Worksheet") LRow = wksQB.Cells(Rows.Count, "A").End(xlUp).Row Set nameRange = wksQB.Range("E2:E" & LRow).SpecialCells(xlCellTypeVisible) For Each rngC In nameRange If Application.CountIf(wksMDR.Range("A:A"), rngC) = 0 Then wksMDR.Cells(Rows.Count, "A").End(xlUp)(2) = rngC End If Next End Sub Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through a filtered range and copy unique values to a second sheet
That works brilliantly! My mind was totally focused on finding a
match (or not); didn't even think about counting. Very nice. As usual, thank you very much, Claus!! Frank On Sat, 4 Jun 2016 11:19:40 +0200, Claus Busch wrote: Sub Macro5() ' Dim wksQB As Worksheet, wksMDR As Worksheet Dim rngC As Range, nameRange As Range Dim LRow As Long Set wksQB = ActiveWorkbook.Sheets("QueryBuster") Set wksMDR = ActiveWorkbook.Sheets("MDR Worksheet") LRow = wksQB.Cells(Rows.Count, "A").End(xlUp).Row Set nameRange = wksQB.Range("E2:E" & LRow).SpecialCells(xlCellTypeVisible) For Each rngC In nameRange If Application.CountIf(wksMDR.Range("A:A"), rngC) = 0 Then wksMDR.Cells(Rows.Count, "A").End(xlUp)(2) = rngC End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop thru filtered sheet, copy unique values to another sheet | Excel Programming | |||
copy unique values into listbox, then modify sheet from these values | Excel Programming | |||
count unique values in a filtered range | Excel Worksheet Functions | |||
Finding unique values from a filtered range | Excel Programming | |||
Paste range of values into filtered sheet | Excel Discussion (Misc queries) |