COUNTIF in VBA #2

I have a userform which adds records to sheet 1.
One of the textboxes is called reference and when i change the contents of
this i want to make sure said contents arent already present in sheet1,col
B.

So i see then that i need to use COUNTIF.

To check if the entry in A1 appears once in col B i used
=COUNTIF(B:B,A1)=1

To check if the entry occurs more than once in the list in col B do i use
=COUNTIF(B:B,A1)>=1

And how do i crowbar this into vba?

For VBA im thinking something along the lines of
formula = "=COUNTIF(B:B,me.reference.value)>=1"

but them im lost on the message box alert.

Thanks to anyone who can show me the way.
Rick

PS apoloies that this is a repost, i really could do with sorting this
before morning.


0
RDS9728 (39)
8/30/2004 7:52:15 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
966 Views

Similar Articles

[PageSpeed] 5

Hi
try
with activesheet
    If application.worksheetfunction.countif( _
       .Range("B:B"),.Range("A1").value) > 0 then
        msgbox "Exist in list"
    end if
end with

--
Regards
Frank Kabel
Frankfurt, Germany


R D S wrote:
> I have a userform which adds records to sheet 1.
> One of the textboxes is called reference and when i change the
> contents of this i want to make sure said contents arent already
> present in sheet1,col B.
>
> So i see then that i need to use COUNTIF.
>
> To check if the entry in A1 appears once in col B i used
> =COUNTIF(B:B,A1)=1
>
> To check if the entry occurs more than once in the list in col B do i
> use =COUNTIF(B:B,A1)>=1
>
> And how do i crowbar this into vba?
>
> For VBA im thinking something along the lines of
> formula = "=COUNTIF(B:B,me.reference.value)>=1"
>
> but them im lost on the message box alert.
>
> Thanks to anyone who can show me the way.
> Rick
>
> PS apoloies that this is a repost, i really could do with sorting
this
> before morning.

0
frank.kabel (11126)
8/30/2004 8:49:51 PM
Hi, the following almost works in my VBA for the reference textbox in my
userform

Private Sub Reference_Change()
With ActiveSheet
    If Application.WorksheetFunction.CountIf( _
       .Range("C:C"), Me.Reference.Value) = 1 Then
        MsgBox "Exist in list"
    End If
End With
End Sub

but the number i need to enter is seven digits with a slash eg 123/456 ut im
getting the exists in list popup as i enter the second digit.

Any ideas,
Thanks,
Rick
"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23eaxiLtjEHA.3140@TK2MSFTNGP15.phx.gbl...
> Hi
> try
> with activesheet
>     If application.worksheetfunction.countif( _
>        .Range("B:B"),.Range("A1").value) > 0 then
>         msgbox "Exist in list"
>     end if
> end with
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> R D S wrote:
> > I have a userform which adds records to sheet 1.
> > One of the textboxes is called reference and when i change the
> > contents of this i want to make sure said contents arent already
> > present in sheet1,col B.
> >
> > So i see then that i need to use COUNTIF.
> >
> > To check if the entry in A1 appears once in col B i used
> > =COUNTIF(B:B,A1)=1
> >
> > To check if the entry occurs more than once in the list in col B do i
> > use =COUNTIF(B:B,A1)>=1
> >
> > And how do i crowbar this into vba?
> >
> > For VBA im thinking something along the lines of
> > formula = "=COUNTIF(B:B,me.reference.value)>=1"
> >
> > but them im lost on the message box alert.
> >
> > Thanks to anyone who can show me the way.
> > Rick
> >
> > PS apoloies that this is a repost, i really could do with sorting
> this
> > before morning.
>


0
RDS9728 (39)
8/31/2004 6:14:11 AM
Reply:

Similar Artilces:

Archiving all messages in Exchange 2003 ? #2
I have enabled archiving of all messages in/out of the store in exchange 2003. However only the outgoing mails seem to appear in the designated public folder. Does anybody know why this is ?? Thanks in advance, Gary ...

outlook 2003 and XP #2
Hello all, I am running XP with outlook 2003. My exchange server is running on 2003 SBS. I have both a POP account and an exchange account. This is working fine but every time I reboot the system the exchange server is always set to default. I put the POP account to default and i can close and open outlook and it stays default but once i reboot the exchange server comes back as default. How do I get it to keep the POP server as default? Thank's Real. ...

email signatures in outlook #2
In outlook 2000 when I create a signature it looks fine but when I open a new mail message the signature is double spaced as opposed to single spaced like I created. What is happening? Thanks Bill Are you using Shift+Enter or Enter when you create the signature? See http://www.slipstick.com/problems/dblspace_html.htm for an explanation. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter ...

How do I get a true calculation? #2
Hi. I think I have just posted this question, but I can't find it now, so my apologies..... I need to start at number 1 and multiply it by 2 - then multiply that answer by 2, then multiply that answer by 2 and carry on until I have done that 480 times!! when I try to do this in excel, I either get the answer 4.72237E+21 (which means nothing to me) or £4,722,366,482,869,650,000,000.0 which isn't a true calculation as I have worked it out up to a point, but I dont have enough spaces on my calculator. Bearing in mind that I have not used excel since I last worked (about 3 ye...

User not accessed the system since last 2 months
Is there any script available to determine accounts that have not logged in for the past x months. Please provide us the link and and guide line how to use that ? Thanks ...

Referencing other Office programs in a Excel VBA App
I din't like the way my previous post sounded, so i'm reposting... All, My Excel VBA programming is decent enough when I'm only working with Excel. However, I'm trying to expand my knowledge base and frequently I wish I could better interact with other Office programs, such as Outlook, in my Excel VBA apps. To better illustrate my question, here's a piece of code that works great (it populates a user form list with Outlook addresses) but I don't fully understand. Private Sub UserForm_Activate() Dim x As Integer Set objOL = CreateObject("...

newbie question IF OR BUT??? #2
thanks - got i -- grilla7 ----------------------------------------------------------------------- grilla79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1478 View this thread: http://www.excelforum.com/showthread.php?threadid=26404 ...

compare 2 lists #2
I import into Excel 2 reports, One report is today's machine status, the other report is yesterdays machine status. Each report has 2 columns. One column is the machine number. The other is the lot number running on the machine. If the lot number changes during the day, that machine number will show up 2 times, once with the old lot number, and once with the new lot number. The only way to tell which is the new lot number, is to look at the old lot on the machine yesterday. I currently do this manually. Is there a function or functions I can use to automate this? Below is an example. Toda...

Mail delay #2
We are using exchange 2003 with outlook clients. There are mails that will take 30 minutes to be available on our outlook clients. We ran a test using hotmail and with one hotmail account, the mail took about 10 seconds. With the other account, the same message took 30 minutes to arrive. Also, this is not just a one time thing. Every time we tested from that "laggy" account, it took more than 20 minutes to arrive. with the other account. it took only seconds. Some other are complaining that it's taking mail a long period of time to be received by the outlook cli...

First Exchange2000 Server in Site #2
Hi! I'm migrating from MXS2000 to MXS2003 and can't find any articles about this topic. I only found some stuff for Exchange5.5. Please help! Thanxs, Peter ...

Business Portal Project Time #2
Import the overtime rules by State when updating Tax info and have Project Time warn users when entering overtime incorrectly. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?m...

CSpinButtonCtrl #2
Env: WindowsXP, VC+6.00, MFC I need to add some processing just when up/down arrow is clicked. From ClassWazard, the only message I can use is UDN_DELTAPOS. Inside OnDeltaposSpin(), I need to know which button between up or down arrow is clicked because I have to get the position after up or down arrow is clicked(NOT the current position of a spin button control. But I don't know how. Please help. TIA William "William" <port@mx15.freecom.ne.jp> wrote in message news:%23kn$837lHHA.4848@TK2MSFTNGP05.phx.gbl... > Env: WindowsXP, VC+6.00, MFC > > I need to a...

Offline Address Lists #2
If I'm in mixed mode with Exchange 2000 and 5.5 and I'm in the process of migrating everything, should I check the 4.0 and 5.0 compatibility box under Recipients/Offline Address Lists? I'm trying to work through an issue where my users, after their mailboxes are moved to the new 2000 Exchange, no longer see the Offline Address Book. I see the Default Offline Address List in my ESM - yet there is no offline address list for my users to download. Thanks, Mark Mark, Pls make sure you OAB Server is also change to exchagen 2000 In ESM\Recipient\Offline Address List>left Panel&...

What converter do I need to change my QPW files to work in Excel 2
I had Quattro Pro (about 2003 -2004) on my computer when my hard drive crashed now I have Excel 2003. What converter do I need to change my QPW files to work in Excel 2003? puzzled Maybe the following will help....depending upon version of QP, I believe. You need the Quattro Pro convertors from MS. http://office.microsoft.com/en-ca/results.aspx?Scope=DC&Query=quattro+pro Then read this from Jim Rech http://snipurl.com/cz3x Gord Dibben Excel MVP On Mon, 12 Dec 2005 06:46:04 -0800, "puzzled" <puzzled @discussions.microsoft.com> wrote: >I had Quattro Pro (about...

Where is the "Import-Export" option on the File menu for Outlook 2
I am trying to create a CSV file to enable me to downloand contacts. I have read numerous times that one uses the "Import and Export" function on the File menu. But, my Outlook '07 v 12.0.6514 does not have that option. I have even seen screen shots showing it (its between "data file management" and "archive") but it doesn't exist in my File menu. Does anyone know where to find "Import and Export"? In my copy it is under 'File' menu "david g" wrote: > I am trying to create a CSV file to enable me to downlo...

X-axis exponential n^1.85 #2
I am currently taking a Fire Hydraulics Course and I am trying to figure out how to create a Graph with the X-axis spacing of n^1.85. I have tried different things and I am now looking for assistance. The graph/chart looks like this PDF: http://www.firesprinkler.org/downloads/AFSA-202.pdf Thanks Chris See my post on the same topic at http://www.mrexcel.com/board2/viewtopic.php?p=526129#526129 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <1160016098.743731.273370@b28g2000cwb.googlegroups...

Error Message #2
I loaded an Excel file (.xls) on another computer. The file will open correctly from within Excel 97 but produces an error message if opened by double clicking the file (via shortcut), which is the preferred method .. When Excel starts and opens the file an error message appears: "A document with the name [file name given] is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that is currently open, or rename one of the documents." On clicking the OK button the f...

bar chart on 2 axis
I am trying to create a bar chart. I have 9 series for which each have 2 sets of data value. I want to create a bar chart where each series is represented by 1 bar, with the chart having one set of values along the x axis and one along the y axis. I am having great difficulty with this. Can you help at all? Full instructions are found at http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Hannah" <Hannah@discussions.microsoft.com> wrote in message news:ED02B24D-AEA9-40E0-8D8C-4199...

IMAP4SVC problem #2
Hello everybody. I'm running MS Exchange 2003 on Server2003. IMAP4SVC in Servces appears as running, however in System Manager IMAP Virtual server is down and can not be started. When I started it from System Manager, it gave me a message: "IMAP4 Configuration The service has returned a service-specific error code. Check the Windows Event Viewer for Details" In Application Log I've found the Warning from Source: IMAP4SVC, Category: General; Event ID: 1036 " An error occured while starting the Microsoft Exchange IMAP4 Service: server instance number 1 failed to s...

Iserror, 0 #2
cool works perfect! The values are unpredictable, so I use ISERROR to clean it up. THANKS -- kkondrat ----------------------------------------------------------------------- kkondrat1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=600 View this thread: http://www.excelforum.com/showthread.php?threadid=26198 ...

Problem with Read receipt #2
I have a customer that as soon as she open her Outlook 2003 it starts sending read receipt notification to another individual. It is being sent every 1 minute and we cannot figure out what causing all this. When outlook is cloe, the read receipt is no longer sent. I have checked for rules and possible virus but no luck. Anybody have an idea where to look or what could cause such a behavior ? Thanks. Try going to Tools/Options then select e-mail options, select tracking options and at the bottom select ask me before sending a response. That might solve your problem. Jose >---...

identities #2
I cannot switch identities <anonymous@discussions.microsoft.com> wrote in message news:187d601c44a89$7be5a8c0$a101280a@phx.gbl... > I cannot switch identities Outlook does not use Identities. Outlook EXPRESS does. This newsgroup is for support of Outlook 97/98/2000/2002/2003 from the Office suite of products. Outlook Express is actually a separate program despite the similar name. For help with your OE questions, try an OE newsgroup such as microsoft.public.windows.inetexplorer.ie6_outlookexpress (for OE 6), or an OE help website such as http://insideOE.tomsterdam.com. If you...

Open 2 independent sessions of EXCEL
Good morning, I looking for a way to open a work book by clicking on it's existing icon or it's shortcut of MYWORKBOOK and have it open in a new session of EXCEL even if an other workbook is already opened. The reason is that this particular workbook has an auto auto macro that edits that when EXCEL looks and I don't wish for any existing opened workbook to be modified also. So what I need is a command or a property of some kind what defines MYWORKBOOK so that when it is called to be opened, it opens in a new session of EXCEL and is independent of the fist one that is running ...

My computer will not install Service Pack 2.
It keeps saying failed to install error code 490. Also OX80070490. I have tried closing out all security but still won't install. I have downloaded and ran service pack 2 installer. Thanks for any help. -- wtb You receive a "0x80070490" error code when you use Windows Update or Microsoft Update Web sites to install updates http://support.microsoft.com/kb/958044 Free unlimited installation and compatibility support is available for Windows Vista, but only for Service Pack 2 (SP2). This support, originally scheduled to end 26 November 2009, has been extended unti...

Recover Deleted Items #2
All of the icons are missing across the top of recovery box so no recovery of items can be done. An error report is generated and when i close that window it restarts microsoft outlook but still doesn't work. Let me know...thanks ...