• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA Countif formula

HI There,
Sorry If i did paste the code incorrectly. I am not too sure if you accept the post as is.
I have a problem with the Countif formula ( which is located in the sub on the second blocK).
It does not show the actual MsgBox content " Order Number already exist!.....
Most likely i have the Exit Sub command incorrectly placed right after the MsgBox.
Would you be able to guide me how i should fix this?
Many Thanks
Martin

>>> use code - tags <<<
You can find - how to do this? ... from
..
as well as
Seems You're interesting about VBA Macros,
then this should have opened in there.
(This time, I've moved it there.)
You could reread Forum Rules from here:

Code:
Private Sub CmdBSaveOrder_Click()

If Me.TxtBox_PolicyNumber = "" Or Me.CmbBox_CustomerNo = "" Or Me.CmbBox_PropertyType = "" _
Or Me.CmbBox_TeamNumber = "" Or Me.TxtBox_OrderNo = "" Then
MsgBox "Data Missing! Please complete information"
Exit Sub
End If

If WorksheetFunction.CountIf(Sheets("Orders").Range("B:B"), Me.TxtBox_OrderNo.Value) > 0 Then
    MsgBox "Order Number already exist ! . Try a new Number"
    Call CmdBNewOrder_Click
Exit Sub
End If


Me.Hide
Application.ScreenUpdating = False
'Collects Data from FORM and loads to 'Calculation-Table'
Range("AB6") = TxtBox_OrderNo.Value
Range("AC6") = DTPicker1.Value
Range("AE6") = TxtBox_PolicyNumber.Value
Range("AF6") = CmbBox_CustomerNo.Value
Range("AI6") = "AMA"
Range("AJ6") = CmbBox_PropertyType.Value
Range("AR6") = CmbBox_TeamNumber.Value

Dim nextBlankRow As Long
lr = Sheets("Orders").Cells(Rows.Count, 2).End(xlUp).Row
nextBlankRow = lr + 1

Range("B" & nextBlankRow) = Range("AB6").Value
Range("C" & nextBlankRow) = Range("AC6").Value
Range("D" & nextBlankRow) = Range("AD6").Value
Range("E" & nextBlankRow) = Range("AE6").Value
Range("F" & nextBlankRow) = Range("AF6").Value
Range("G" & nextBlankRow) = Range("AG6").Value
Range("H" & nextBlankRow) = Range("AH6").Value
Range("I" & nextBlankRow) = Range("AI6").Value
Range("J" & nextBlankRow) = Range("AJ6").Value
Range("K" & nextBlankRow) = Range("AK6").Value
Range("L" & nextBlankRow) = Range("AL6").Value
Range("M" & nextBlankRow) = Range("AM6").Value
Range("N" & nextBlankRow) = Range("AN6").Value
Range("O" & nextBlankRow) = Range("AO6").Value
Range("P" & nextBlankRow) = Range("AP6").Value
Range("Q" & nextBlankRow) = Range("AQ6").Value
Range("R" & nextBlankRow) = Range("AR6").Value
Range("S" & nextBlankRow) = Range("AS6").Value
Range("T" & nextBlankRow) = Range("AT6").Value

Range("B8", Range("B8").End(xlDown)).Sort Key1:=Range("B8"), _
Order1:=xlAscending, Header:=xlYes

Application.ScreenUpdating = True

MsgBox "New Record has been added Succesfully"
Call CmdBNewOrder_Click
Unload Me
End Sub
 
Last edited by a moderator:
A guess is that it could be that column B contains numeric data while the textbox contains text (but looking like numbers) so it doesn't find the text amongst the numbers.
A sample workbook would be a good idea.
 
It looks as though you are doing a straightforward data entry into an Excel worksheet. Have you considered using the built-in Form function?
 
HI there for your reply

My excel File is 2 Mb big. Is too large to be attached. How else can I send it to you?
As you implied in your last email (thanks), I do have built-in Data capture forms.
I'd love to share them with you, please.

Many Thanks in advance
Regards
Martin
 
My excel File is 2 Mb big. Is too large to be attached. How else can I send it to you?
Either:
remove unnecessary sheets, reduce the number of rows of data while ensuring that your problem still obtains, then try attaching again (an xlsb file is smaller than the corresponding xlsm file)
or:
use a file sharing site or a cloud based drive (onedrive/google drive etc.) and post a link. If you don't want it in the public domain password protect it and pass the password via a private conversation here
or:
as a real last resort, ask for a real email address to send the file to.
 
With 1400 lines of code, 30 sheets/forms/modules you could have pointed us where to look!
So far the message comes up for me if the order number already exists, despite the text box containing text and column B containing numbers.
It doesn't appear if I use a number which doesn't exist.
So in what circumstances isn't it workingas you wish?
 
Indeed I should have included my problem with the link, sorry i just wanted to know if you were able to open the file.
The problem is in fact in the 'Orders', mores specifically i the Form that is used to populate this spreadsheet.
The code behind the 'Orders' Form ( specifically) : ( see Underlined code)

>>> use code - tags <<<
Code:
Sub Private Sub CmdBSaveOrder_Click()

If Me.TxtBox_PolicyNumber = "" Or Me.CmbBox_CustomerNo = "" Or Me.CmbBox_PropertyType = "" _
Or Me.CmbBox_TeamNumber = "" Or Me.TxtBox_OrderNo = "" Then
MsgBox "Data Missing! Please complete information"
Exit Sub
End If

If WorksheetFunction.CountIf(Sheets("Orders").Range("B:B"), Me.TxtBox_OrderNo.Value) > 0 Then
    MsgBox "Order Number already exist ! . Try a new Number"
    Call CmdBNewOrder_Click
Exit Sub
End If

I can't get it to bring up the MsgBox "Order Number already exist !......"
it puts s a cero '0' in the actual TxtBox_OrderNo ( control) , but it does not show me the message box.

That is the only problem that i have relating to the code behind the form
Does the End if statement need to be at the end of this Sub?

Many Thanks in advance for your help. rods
Martin
 
Last edited by a moderator:
I did say I'd got that far:
So far the message comes up for me if the order number already exists, despite the text box containing text and column B containing numbers.
It doesn't appear if I use a number which doesn't exist.
When you first bring up the form, it comes up with an entry. If you straightway click on Save Order without changing anything else do you get the message then?
So far I can't reproduce your problem.

ps. You're not trying to do this on the web are you?! (I don't think you can be because the user form wouldn't pop-up anyway)
 
Indeed. I am not using the web interface as ‘Activex ‘ controls do not work on the ‘on line’ environment.
Would it be too much to ask u , if u can wait until next week , since i need to get back my ‘Lenovo’ ( windows laptop).
I’m testing with my ‘MacBook’ and is a bit of a nightmare .
Will i need to open a new ‘ticket’?
Sorry to trouble
 
Hi p45cal
I'm back ... sorry for the delay . My new laptop is now working.
I can see now that the problem is in the Private Sub CmdBSearch_Click ()..... (I've attached a screen shot highlighting the problem).
It does not bring up the message box "Order NOT found"..., if i type an Order that does not exist ( That is the only problem).
Thanks and sorry to trouble u.
Martin
 

Attachments

  • Private Sub CmdBSearch.png
    Private Sub CmdBSearch.png
    151.5 KB · Views: 4
Well, just looking at your picture, the line directly above the line you highlighted is a line which makes the two things you're comparing the same, so of course your highlighted line will never be true! [despite one being a string and the other a number]

I suspect, but haven't examined in detail, that you want that check to be after you've actually looked for the order in:
Set findvalue = Sheets("Orders").Range("B:B").Find

Then you could either check for findvalue being Nothing:
If findvalue is Nothing then

or later still you have:
If WorksheetFunction.CountIf(Sheets("Orders").Range("B:B"), Me.TxtBox_OrderNo.Value) > 0 Then
which also serves as a check that the order exists.
 
HI once again,
Believe me i have tried everything , from the If findvalue is Nothing then , to chnage the type of variable to 'string' ( using the 'InputBox' to changing the Variable Ordertofind to an Integer , using the 'Application.Inout Box', etc.
Most of the time i get an error that cannot recognise "orderttofind"
I have copied the 'streamlined' version of the code underneath , ( i deleted a few lines of codes which were a bit repetitive).
Would you be able to run it on your side and let me know where the problem could be, perhaps.
Thanks very much...
Note: Revised code as follows:

as noted
>>> use code - tags <<<

Code:
Private Sub CmdBSearch_Click()

Call CmdBNewOrder_Click
Range("B8", Range("B8").End(xlDown)).Sort Key1:=Range("B8"), _
Order1:=xlAscending, Header:=xlYes

Dim Ordertofind As String
On Error GoTo Cancelled
Ordertofind = InputBox(Prompt:="Enter Order Number to find", Title:="Order Number to Find")

Dim findvalue As Range
Set findvalue = Sheets("Orders").Range("B:B").Find(what:=Ordertofind, LookIn:=xlFormulas, lookat:=xlWhole)
If Not Ordertofind Is Nothing Then

        TxtBox_OrderNo = findvalue
        TxtBox_OrderDate.Text = findvalue.Offset(0, 1)
        TxtBox_Period.Text = findvalue.Offset(0, 2)
        TxtBox_PolicyNumber = findvalue.Offset(0, 3)
        CmbBox_CustomerNo = findvalue.Offset(0, 4)
        TxtBox_CustomerLastName = findvalue.Offset(0, 5)
        TxtBox_CustomerFirstName = findvalue.Offset(0, 6)
        TxtBox_Park = findvalue.Offset(0, 7)
        CmbBox_PropertyType = findvalue.Offset(0, 8)
        TxtBox_PropertyName = findvalue.Offset(0, 9)
        TxtBox_SectorLot = findvalue.Offset(0, 10)
        TxtBox_ParcelTotals = findvalue.Offset(0, 11)
        TxtBox_BurialSites = findvalue.Offset(0, 12)
        TxtBox_Cash = findvalue.Offset(0, 13)
        TxtBox_Premium = findvalue.Offset(0, 14)
        TxtBox_PayOut = findvalue.Offset(0, 15)
        CmbBox_TeamNumber = findvalue.Offset(0, 16)
        TxtBox_Comm_Boss = findvalue.Offset(0, 17)
        TxtBox_Comm_Agent = findvalue.Offset(0, 18)

activerow = findvalue.Row
Else
MsgBox "Order Number NOT found"
End If

Cancelled:
End Sub
 
Last edited by a moderator:
Wow !.. Thanks so much. it definitively works !
So obvious , isn't it?, and yet sometimes we become blind.
Only Experts like you can help us see the obvious.
Once again, thanks a million
Kind regards. Martin
 
Back
Top