VBA adjustment for beginner
Good evening All
I am hoping that someone will be able to help with this. I have some small VBA code (shown below), which applies to the following example of a data set: Worksheet "Invoice Record" A B C 1 Header Header Header 2 M100 152.34 03 3 M101 100.02 NP 4 M100 250.65 02 5 M100 565.52 01 6 M102 745.87 NP 7 M101 985.65 03 Column A is Formatted Text, B Number, C Text. (Though columns A & C are flexible to other formats) The code describes:- from column A copy unique values only to another location (another Worksheet ("General Report") in this case) This works well, however I would really like the code to look at only the cells in column A that do not have NP as it's corresponding entry in Column C. ie to ignore rows with NP in column C I do hope that this is an easy fix, I am a novice at VBA but am learning! (Especially with help from this group) Here Is the Code: Sub ECRGeneralReportPopulation() ' Automates The General Report Population Application.Interactive = False Dim myRng As Range Sheets("General Report").Range("A:A").ClearContents With Sheets("Invoice Record") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With myRng.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("General Report").Range("A2"), Unique:=True Application.Interactive = True End Sub I hope someone can help Cheers Mathew |
VBA adjustment for beginner
Matthew,
Tricky one. I think you would be better to use AUtofilter, but one key question. If you only want to select where C =NP, will the unique test apply to all rows, or only rows with NP in C. As an example, if A1=M100 and A2=M100, C1 = "NP" and C2="O1", is M100 unique or not? -- HTH Bob Phillips "mpb1" wrote in message ... Good evening All I am hoping that someone will be able to help with this. I have some small VBA code (shown below), which applies to the following example of a data set: Worksheet "Invoice Record" A B C 1 Header Header Header 2 M100 152.34 03 3 M101 100.02 NP 4 M100 250.65 02 5 M100 565.52 01 6 M102 745.87 NP 7 M101 985.65 03 Column A is Formatted Text, B Number, C Text. (Though columns A & C are flexible to other formats) The code describes:- from column A copy unique values only to another location (another Worksheet ("General Report") in this case) This works well, however I would really like the code to look at only the cells in column A that do not have NP as it's corresponding entry in Column C. ie to ignore rows with NP in column C I do hope that this is an easy fix, I am a novice at VBA but am learning! (Especially with help from this group) Here Is the Code: Sub ECRGeneralReportPopulation() ' Automates The General Report Population Application.Interactive = False Dim myRng As Range Sheets("General Report").Range("A:A").ClearContents With Sheets("Invoice Record") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With myRng.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Sheets("General Report").Range("A2"), Unique:=True Application.Interactive = True End Sub I hope someone can help Cheers Mathew |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com