VBA adjustment for beginner

  • Thread starter Thread starter mpb1
  • Start date Start date
M

mpb1

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
 
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

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
 
See one more guess at your post in .misc.
mpb1 wrote:

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
Click to expand...
 
Back
Top