Adjustmant To VBA using AdvancedFilter

This is a multi-part message in MIME format.

------=_NextPart_000_0124_01C35208.FC27F940
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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     =20
1    M100    152.34        03
2    M101    100.02        NP
3    M100    250.65        02
4    M100    565.52        01
5    M102    745.87        NP
6    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 =3D False
Dim myRng As Range
Sheets("General Report").Range("A:A").ClearContents
With Sheets("Invoice Record")
Set myRng =3D .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
myRng.AdvancedFilter Action:=3DxlFilterCopy, _
CopyToRange:=3DSheets("General Report").Range("A2"), Unique:=3DTrue
Application.Interactive =3D True
End Sub
=20
I hope someone can help
Cheers
Mathew
------=_NextPart_000_0124_01C35208.FC27F940
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Good evening All</FONT></DIV>
<DIV><FONT size=3D2>I am hoping that someone will be able to help with=20
this.</FONT></DIV>
<DIV><FONT size=3D2>I have some small VBA code (shown below), which =
applies to the=20
following example of a data set:</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Worksheet "Invoice Record"</FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;=20
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&nbsp;&nbsp;&nbsp;=20
C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT size=3D2>1&nbsp;&nbsp;&nbsp;=20
M100&nbsp;&nbsp;&nbsp;&nbsp;152.34&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=20
03</FONT></DIV>
<DIV><FONT size=3D2>2&nbsp;&nbsp;&nbsp; M101&nbsp;&nbsp;&nbsp;=20
100.02&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; NP</FONT></DIV>
<DIV><FONT size=3D2>3&nbsp;&nbsp;&nbsp; M100&nbsp;&nbsp;&nbsp;=20
250.65&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 02</FONT></DIV>
<DIV><FONT size=3D2>4&nbsp;&nbsp;&nbsp; M100&nbsp;&nbsp;&nbsp;=20
565.52&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 01</FONT></DIV>
<DIV><FONT size=3D2>5&nbsp;&nbsp;&nbsp; M102&nbsp;&nbsp;&nbsp;=20
745.87&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; NP</FONT></DIV>
<DIV><FONT size=3D2>6&nbsp;&nbsp;&nbsp; M101&nbsp;&nbsp;&nbsp;=20
985.65&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 03</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Column A is Formatted Text, B Number, C Text. =
(Though columns=20
A &amp; C are flexible to other formats)</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>The code describes:-&nbsp;&nbsp;from column A copy =
unique=20
values only to another location (another Worksheet ("General =
Report")&nbsp;in=20
this case)</FONT></DIV>
<DIV><FONT size=3D2>This works well, however I would really like the =
code to look=20
at only the cells in column A that <STRONG>do not </STRONG>have NP as =
it's=20
corresponding entry in Column C.</FONT></DIV>
<DIV><FONT size=3D2>ie to ignore rows with NP in column C</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>I do hope that this is an easy fix, I am a novice at =
VBA but=20
am learning! (Especially with help from this group)</FONT></DIV>
<DIV><FONT size=3D2>Here Is the Code:</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Sub ECRGeneralReportPopulation()<BR>'&nbsp;&nbsp; =
Automates=20
The General Report Population<BR><BR>Application.Interactive =3D =
False<BR>Dim=20
myRng As Range<BR>Sheets("General =
Report").Range("A:A").ClearContents<BR>With=20
Sheets("Invoice Record")<BR>Set myRng =3D .Range("A2", =
..Cells(.Rows.Count,=20
"A").End(xlUp))<BR>End With<BR>myRng.AdvancedFilter =
Action:=3DxlFilterCopy,=20
_<BR>CopyToRange:=3DSheets("General Report").Range("A2"),=20
Unique:=3DTrue<BR>Application.Interactive =3D True<BR>End =
Sub</FONT></DIV>
<DIV><FONT size=3D2>&nbsp;</FONT></DIV>
<DIV><FONT size=3D2>I hope someone can help</FONT></DIV>
<DIV><FONT size=3D2>Cheers</FONT></DIV>
<DIV><FONT size=3D2>Mathew</FONT></DIV></BODY></HTML>

------=_NextPart_000_0124_01C35208.FC27F940--

0
mpb14728 (11)
7/24/2003 4:27:58 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
372 Views

Similar Articles

[PageSpeed] 34

I think I'd use a helper column that contains a formula that discounts the
NP's.  Then advance filter on that, delete the help column, then cleans up the
advanced filter.

Option Explicit
Sub ECRGeneralReportPopulation()
'   Automates The General Report Population

Application.ScreenUpdating = False

Dim myRng As Range
Dim FoundCell As Range
Dim dummyStr As String

dummyStr = "zzzzzzzzzzzzz"

Worksheets("General Report").Range("A:A").ClearContents

With Worksheets("Invoice record")
    .Columns(1).Insert
    With .Range("A1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        .Formula = "=if(d1=""np"",""" & dummyStr & """,b1)"
        .Value = .Value
    End With
    
    Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))

    myRng.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Sheets("General Report").Range("A2"), Unique:=True
    
    .Columns(1).Delete
End With

With Worksheets("general report").Range("a:a")
    Set FoundCell = Nothing
    Set FoundCell = .Cells.Find(what:=dummyStr, LookIn:=xlValues, _
                lookAt:=xlWhole, MatchCase:=False, _
                after:=.Cells(.Cells.Count), _
                searchdirection:=xlPrevious)
    If FoundCell Is Nothing Then
        'do nothing
    Else
        FoundCell.EntireRow.Delete
    End If
        
End With

Application.ScreenUpdating = True

End Sub

Be careful.  I still don't know where your headers are and where your data
really starts.  (And I changed application.interactive to
application.screenupdating.

The screenupdating = false hides the flickering of the screen.

> 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    M100    152.34        03
> 2    M101    100.02        NP
> 3    M100    250.65        02
> 4    M100    565.52        01
> 5    M102    745.87        NP
> 6    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

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/25/2003 1:51:49 AM
Reply:

Similar Artilces:

using Publisher for web development?
Hello, I maintained a web site using MS Words before, but now want to be a little bit more serious in developing my own web site. I tried Publisher, which seemed to focus many things other than web development Does that imply Publisher lacks some fundamental features in web development? For example, in MS Words, I can divide a page into frames and then port different web pages into these frames in response to users mouse input. I viewed it as a fundamental feature but didn't see it in Publisher. Maybe I overlook it But in general, what people suggest about using Publisher to d...

Reset last row used
I have a worksheet where I have deleted the data form the last batch of rows and wish to reset (using VBA) the last row used returned by Excel 2007 to the genuinely occuped rows. I have tried using: ActiveSheet.UsedRange but it doesn't do the trick. That is probably because the rows from which I have deleted the data have still retained their formatting. I do want to retain the formatting (eg row height etc) as I wish to add further data in due course. Any suggestions as to how I can reset what Excel returns as last row used in these circumstances? TIA Chris ...

Smart list downloads, how to use?
I have downloaded some smartlists from Microsoft for the manufacturing tables. They are .xml files. How do I import them into Great Plains? -- Sheri Salomone THANKS! Assuming SLB is installed, go to Tools >> SmartList Builder >> Import. Select the file you want to import. Please read the SmartList Builder Users Guide beginning on Page 40. "Sheri Salomone" <SheriSalomone@discussions.microsoft.com> wrote in message news:FE70A3D5-230C-4375-B00C-CAF313C877AB@microsoft.com... >I have downloaded some smartlists from Microsoft for the manufacturing > tables....

using CRM email
When using CRM email, when it comes out of our exchange server has "SPAM:" in front of the subject and "CRM 0000000" (crm number) at the end of the subject. How can we stop that from happening. Hi Richard, that, obviously is the works of your anti-spam software installed on the Exchange and you need to tell this software not to mark internally originated emails as spam. The reason why CRM emails are marked as spam is precisely because of the unique number at the end. A lot of anti-spam systems raise spam ranking significantly once they detect identifiers in the sub...

I'm trying to use combo boxes to select info to be shown in a text
I'm trying to use a group of combo boxes to select a count of a query in a form or report. Currently I have three variables on the form I want to use and have a text box show the counts of instances within a particular query. =DCount("[MXGPC-WKCTR STRIPPED] & [DAFSC] & [ACT-GR]","qry4monthprojectionloss","[MXGPC-WKCTR STRIPPED] = 'rptfrm.form![1st Workcenter]' and [ DAFSC]='rptfrm.form![1st DAFSC]' and [ACT-GR]= 'rptfrm.form![ACT-GR1]'") If I replace the form refrences (rptfrm.fomr![1st Workcenter]) with specific c...

How to use comma separated inputs in macro
Hello I have strange requirement. I have a column with hundreds of entries in it. There is one other columns associated eith it. For an example consider that following are the entries in column A and B Column A Column B PI03590 bar ZT05693 % PIC098734 N TT09823 C Now I have to provide one userform input box to user. The user will enter entries separated by comma (or any other character). I have to filter entres in column B as per these input...

memory dump by using ctrl+scrlk+scrlk
To troubleshoot a server (w2k8, sp2) I have set up to do memory dump using ctrl+scrlk+scrlk and dedicated dump file. I tested it and it triggered the dump. But I expected the server to reboot itself at the end but it didn't. It showed 100% complete and stayed at the blue screen. I had to manaully power it down/on. Is it supposed to like this? After reboot a new memory.dmp was created. I guess it is working but just curious if it should reboot itself once done. Any idea? Thanks. Chris;1259949 Wrote: > To troubleshoot a server (w2k8, sp2) I have set up to do...

I am using excel 2003 and am trying to return a number associated with a column by comparing two columns.
I am using excel 2003 and am trying to return a number associated with a column by comparing two columns. Specifically, in the screenshot linked below, I am trying to populate column D with the number in column F where column E matches column A. Essentially, I want column D to contain the numbers in column H (which I typed as reference only to help you help me!). http://www.series11.net/files/ExcelExample.JPG Thanks in advnace!! >I am using excel 2003 and am trying to return a number associated with > a column by comparing two columns. > > Specifically, in the screenshot linke...

Using vba find
I couldn't understand why the find method wasn't working in a macro until I realised that the range of cells I was searching didn't contain actual text, but the result of formulae referring to text in another worksheet. Is there a way of using find so that it will identify if the result of a formula matches the text you're searching for? Grateful for advice. Set SearchRange = Sheets("sheet1").Range("A1:A100") set c = SearchRange(what:="ABC", lookin:=xlvalues,lookat:=xlwhole) if c is nothing then msgbox("Nothing found") ...

Combo Box Query in VBA
I'm having trouble with the multiple criteria in this code. It isn't returning the record I know is there. Me.PWIDTH6.RowSource = "Select distinct parts.lheight " & _ "FROM parts " & _ "WHERE parts.ItemDesc = '" & descrip.Value & "' " & _ "AND parts.pwidth = '" & Text2.Value & "' " & _ "ORDER BY parts.lheight;" Can anyone help me? Thanks in advance, Bonnie Is the field "pwidth" type is numeric? If so you need to drop the single quote, th...

Error
Any help appreciated I suspect this has something to do with either permissions and/or excel setting but ... HTML (or CSV) file is saved to network share Attempt to open this file using Excel ( 2000 or XP ) and get information (i) error message "The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding." If file is saved to local drive, there is no problem opening this file from within Excel How can we open this file direct from Excel? ...

VBA Learning Resources
I notice a lot of the solutions in this NG involve macros programmed in VBA. Can you recommend any books or other learning materials for VBA? Carlos Hi Carlos Below you find links to the best Excel sites and to a book list. Start with books from John Walkenbach. ******************************** From Norman Harker: Here's a bit of a summary collation of Excel resources posted originally in response to a question on how to become an Excel Expert. Perhaps the very best resource is yourself! Few experts have gained their in-depth knowledge other than by banging their heads on the keyb...

Busy status when using other program fullscreen
I'm using vista and my messenger wont change the status from online to busy when ii use another program fullscreen. The settings are correct, but it just doesnt change status ? It's to bad it doesnt change, often people want to talk to me when im using another program fullscreen. So if the status does change, they know im busy and they have to wait for an answer. Greetings Maurice, May I enquire which application's full-screen feature you're using? Depending on how the full-screen mode is done in the application, Messenger may not necessarily detect it (althou...

Outlook and Access integration using VBA
Hi there, in our access database i have code that allows a user to create an outlook appointment, which can be seen below: Dim objOutlook As Object Dim objItem As Object Set objOutlook = CreateObject("Outlook.Application") Set objItem = objOutlook.CreateItem(olAppointmentItem) With objItem .Subject = Title & " " & ": Appointment" End With objItem.Display Set objOutlook = Nothing however, what would be really useful is if it could go one step further and open existing calendar items rather thana new...

formulas using more than one worksheet
I am trying to work out a formula that calculates a figure from a certain starting point in one worksheet and return the value in another worksheet my formula so far is =sum(B12-F11)+STAPLES!H40 I need the formula to continue forever after H40 but am struggling to work it out. Any help would be much appreciated. Best regards Viv continue forever ??? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Eclaires" <Eclaires@discussions.microsoft.com> wrote in message news:6C911408-17A4-46DF-B693-B60671CB158F@microsoft.com... ...

Using And/Or with Check Box
I have a form (form1) with a subform (subform2) and another subform (subform3) On subform2 there is a text box for mom's employer's name. On subform3 there is a text box for dad's employer's name. On form1 there is a check box (Check0) that needs to be filled in automatically when either mom or dad has an employer, or left blank (false) if neither one has an employer. I have tried several different solutions including: Nz(Subform1.Form![Employer], Nz(Subform2.Form![Emplyer], False)) IIf (Not IsNull(Forms![Form1]![Subform1].Form![Employer]) And Not IsNull (Forms![Form1]!...

Using SafeArray to transfer struct
Hi, I want develop an ActiveX with method Set/Get for reading a struct or Object, I think that I should use a SafeArray but I don't know do it. Can anyone help me ? Thanks in advance, Gianfranco ...

Using contacts across Business Units
Hi CRM gurus, does anybody know how to setup the business unit structure for the following scenario in MS CRM 3.0: one parent organisation with two childs (business unit A and B). members of the parent should see all contacts members of business unit A should see only their records and NO records of business unit B members of business unit B should see only their records and NO records of business unit A so far no problem to set this up, but here comes the real problem. In addition to the contacts of business unit A and B there are around 10.000 contacts, which should be seen and used by ...

Use OWA for personal email
I was wodnering if it is possible to use Outlook 2007 to retreive web based emails (gmail) and be able to access Outlook through the web via a different computer (OWA). I am using XP pro on my personal computer with Office Pro 2007. outlook will access gmail OWA works ONLY with an Exchange account "mcm52188" wrote: > I was wodnering if it is possible to use Outlook 2007 to retreive web based > emails (gmail) and be able to access Outlook through the web via a different > computer (OWA). I am using XP pro on my personal computer with Office Pro > 2007...

using xsd schema with integrity constraint
Hi there, I would like to know if the integrity constraint in schema can be used in Excel in order to check them on a xls file mapped with this schema ? What I mean for instance is : map a xls file with an xsd schema which states that a "column" is a unique key, and then validate this file against this schema to verify that the values in that column are really unique. Best regards ...

Unable to use DynamicEntity() to exeute a response
Hi, My CRM client has two custom entities set up, and I can use Dynamic Entity to create a record against those custom entities, respectively. Now, I created an entity for generating a sequence number and the plug- in will read the value and parse the incremented value to those entities. The original entities are related to "account" and "contact". The one that I created does not associated to any entity. For those two entities, I can able to create or update record with using Dynamic entity. However, the one that I create (for the sequece number) is not. I am able to ret...

Using MFC in a Managed C++ DLL?? Can I?
I have some old C code that uses MFC and some C Run Time Library. I need to be able to call this from C# I thought i'd be able to create a Managed C++ DLL, tell the project it was using MFC, include the .c file and be on my way. Can I use MFC in a Managed C++ DLL? If I build a Visual C++ Class Library (.Net) and tell it to use MFC in a Shared DLL I immediately get the following warning nochkclr.obj : warning LNK4099: PDB 'libc.pdb' was not found with 'C:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\lib\nochkclr.obj' or at 'C:\BlueIguana\SymbolPDTSolution\Deb...

Proxy Authentication / Upload data using HTTP
I am facing problem for conneting to server using WinInet, via Proxy, I am using proxy to connect to HTTPS server, Code: hOpenHandle = InternetOpen("Example", INTERNET_OPEN_TYPE_PRECONFIG, "PxoryName", NULL); hConnectHandle = InternetConnect(hOpenHandle, "www.sever.com" 443, "username", "serverName", INTERNET_SERVICE_HTTP, ...

How do I "Double-click" only using the keyboard?
I want to be able to click through a cell to the referenced cell without having to reach for my mouse, put the cursor in the cell, and double-click. I want to be able to hit a keyboard shortcut, and have excel take me to the referenced cell. Is this possible? Can you give an example? I'm confused as to what you're exactly trying to accomplish. "mmrosek" wrote: > I want to be able to click through a cell to the referenced cell without > having to reach for my mouse, put the cursor in the cell, and double-click. > I want to be able to hit a keyboard shortcu...

how can I use a number series with gaps in as a chart axis
I have a spreadsheet with samples normally taken on a weekly basis. Rather than plot the data against the week I want to plot it against a running hours figure that is also entered onto the sheet. However if no samples have been taken this leaves a blank and a gap in my line chart for trending and doesn't seem to allow the running hours to be used as numbers but just sees them as a piece of text or category. I want to see the dat against running hours at the correct spacing of hours. I'm sure that made sense to me as I wrote it!! any help will be appreciated Make an XY chart. ...