Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop thru filtered sheet, copy unique values to another sheet Phrank Excel Programming 0 June 4th 16 12:58 AM
copy unique values into listbox, then modify sheet from these values Matthew Dyer Excel Programming 4 September 28th 09 04:11 PM
count unique values in a filtered range ChrisR Excel Worksheet Functions 2 July 1st 08 07:02 PM
Finding unique values from a filtered range Rich[_30_] Excel Programming 0 May 11th 06 03:53 PM
Paste range of values into filtered sheet ken2005 Excel Discussion (Misc queries) 3 October 3rd 05 11:22 AM


All times are GMT +1. The time now is 07:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"