Export to Excel, and add a cell to 'count if'

Posted on 2011-10-10
Last Modified: 2013-12-18
I have a working code that exports records to Excel.  I have been asked to add columns that do simple calculations.  The only one I can't get to work is COUNTIF.


For example, the code reads
'This works
xlsheet.Cells(3,2).Value = "=SUM(A1:A13)"
xlsheet.Cells(4,2).Value = "=COUNTA(A14:A20)

'This throws an error
xlsheet.Cells(11,2).Value = "=(countif(c3:c13),"Y")"

the error is:
Unexpected Y: Expected End-Of-statement: Operator

I can't find anything in lotusscript references.  This is the last piece of this project - I'm desparate!  Is this possible?
Question by:jkee54
    LVL 15

    Expert Comment

    by:Haris Djulic
    try this:

    xlsheet.Cells(11,2).Value = "=countif(c3:c13;"Y")"

    Author Comment

    Still gives the same error.  Seems like it doesn't recognize COUNTIF
    LVL 3

    Expert Comment

    try this:

    xlsheet.Cells(11,2).Value = "=countif(c3:c13;""Y"")"

    LVL 3

    Expert Comment

    or better:

    xlsheet.Cells(11,2).Value = "=countif(c3:c13,"Y")"

    Just saw that your separator is a comma instead of ;


    Author Comment


    It didn't throw that same error, but it does stop the code.  Debugger doesn't show anything, but the client shows an error, "OLE Automation error".

    Author Comment

    OOPS I meant it does not stop the code - it ran all the way, but didn't insert the formula in the right cell, and then had the OLE error.  
    LVL 3

    Expert Comment

    I just saw I messed up my responses a bit, did you use this code?

    xlsheet.Cells(11,2).Value = "=countif(c3:c13,""Y"")"

    It should put this formula into cell B12, as a test just pointed out.
    Maybe you could change Value to Formula.
    Or maybe there's something else in your code that is causing the error?
    Can you share the rest of the code?

    Author Comment

    I tried that last line with my correct range, so it's modified as follows and the code runs with no error:

          xlsheet.Cells(11,2).Value = "=countif(C11:Z11,""Y"")"

    The problem is still though, it doesn't do the calculation.  There were Y's in columns E and F, so the cell should have had 2 as the formula result.

    I also tried Formula instead of Value, same thing - no error, but no result.

    It's not as though it doesn't calculate - the formulas just don't show up in the cell.  The COUNTA formulas don't show either.

    I'm attaching the code this time. I will clean up the formatting later - it's quite long but I hope you can help.
    Sub Click(Source As Button)
    'Export the selected documents from view to Excel spreedsheet
    	Dim session As New notessession
    	Dim db As notesdatabase
    	Dim doc As notesdocument
    	Dim xlApp As Variant
    	Dim xlsheet As Variant
    	Dim xlrange As Variant
    	Dim xlobj As Variant
    	Dim filename As String
    	Dim DC As NotesDocumentCollection
    	Set db=session.currentdatabase
    	Set dc=db.unprocesseddocuments
    	Print "Collection size: " &dc.Count
    	Set xlApp=CreateObject("Excel.application")
    	Set xlsheet=xlApp.Workbooks(1).Worksheets(1)
    'Load column and row headers
    ' Row titles
    	xlsheet.Cells(2,1).Value = "Underwriter Name"
    	xlsheet.Cells(2, 1).font.bold = True
    	xlsheet.Cells(3,1).Value = "Policy"
    	xlsheet.Cells(3, 1).font.bold = True
    	xlsheet.Cells(4,1).Value = "Face"
    	xlsheet.Cells(4, 1).font.bold = True
    	xlsheet.Cells(5,1).Value = "Age"
    	xlsheet.Cells(5, 1).font.bold = True
    	xlsheet.Cells(6,1).Value = "Client Last Name"
    	xlsheet.Cells(6, 1).font.bold = True
    	xlsheet.Cells(7,1).Value = "Site"
    	xlsheet.Cells(7, 1).font.bold = True
    	xlsheet.Cells(8,1).Value = "Summary of Case"
    	xlsheet.Cells(8, 1).font.bold = True
    	xlsheet.Cells(9,1).Value = "FINAL DECISION"
    	xlsheet.Cells(9, 1).font.bold = True
    	xlsheet.Cells(10,1).Value = "  FINAL DECISION APPROPRIATE"
    	xlsheet.Cells(10, 1).font.bold = True
    	xlsheet.Cells(11,1).Value = "     Appropriately developed and assessed per published guidelines. Good und. Judgement applied to determine overall risk class"	
    	xlsheet.Cells(12,1).Value = "     Underwriter utilized all available options for best class "
    	xlsheet.Cells(13,1).Value = "     Superior Handling"
    	xlsheet.Cells(14,1).Value = "  FINAL DECISION -MINOR"
    	xlsheet.Cells(14, 1).font.bold = True
    	xlsheet.Cells(15,1).Value = "      Underwriting decision  within 1 risk class per published guidelines"
    	xlsheet.Cells(16,1).Value = "  FINAL DECISION - MAJOR"
    	xlsheet.Cells(16, 1).font.bold = True
    	xlsheet.Cells(17,1).Value = "      Underwriting decision  2 or more risk classes outside published guidelines"
    	xlsheet.Cells(18,1).Value = "      Unable to determine if final decision is appropriate due to lack of  development"
    	xlsheet.Cells(19,1).Value = "  FINAL DECISION - OPPORTUNITY FOR IMPROVED OFFER"
    	xlsheet.Cells(19, 1).font.bold = True
    	xlsheet.Cells(20,1).Value = "      There were favorable aspects of the case that could have allowed for a better class than suggested by published guidelines"
    	xlsheet.Cells(21,1).Value = "DOCUMENTATION"
    	xlsheet.Cells(21, 1).font.bold = True
    	xlsheet.Cells(22,1).Value = "  DOCUMENTATION APPROPRIATE"
    	xlsheet.Cells(22, 1).font.bold = True
    	xlsheet.Cells(23,1).Value = "      Adequately documented to justify overall decision."
    	xlsheet.Cells(24,1).Value = "  DOCUMENTATION NEEDS IMPROVEMENT "
    	xlsheet.Cells(24, 1).font.bold = True
    	xlsheet.Cells(25,1).Value = "      Documentation insufficient to support decision. All pertinent aspects of risk assessment not commented on."
    	xlsheet.Cells(26,1).Value = "DEVELOPMENT"	
    	xlsheet.Cells(26, 1).font.bold = True
    	xlsheet.Cells(27,1).Value = "  DEVELOPMENT MAJOR"
    	xlsheet.Cells(27, 1).font.bold = True
    	xlsheet.Cells(28,1).Value = "      Oversight is one that is contrary to established practice (without adequate documentation justifying the departure), and could have impacted the final underwriting decision significantly."
    	xlsheet.Cells(29,1).Value = "  DEVELOPMENT MINOR"
    	xlsheet.Cells(29, 1).font.bold = True
    	xlsheet.Cells(30,1).Value = "      Requirements not ordered in a timely fashion ."
    	xlsheet.Cells(31,1).Value = "      Oversight is one that is contrary to established practice (without adequate documentation justifying the departure), but most likely would not have impacted the final underwriting decision. ."
    	xlsheet.Cells(32,1).Value = "FINANCIAL / SUITABILITY"	
    	xlsheet.Cells(32, 1).font.bold = True
    	xlsheet.Cells(33,1).Value = "      Product/amt in relationship to age/finances/purpose/need  developed/assessed appropriately."
    	xlsheet.Cells(34,1).Value = "      Unadmitted replacement not developed or referred to replacement analyst."
    	xlsheet.Cells(35,1).Value = "Amendment Forms"	
    	xlsheet.Cells(35, 1).font.bold = True
    	xlsheet.Cells(36,1).Value = "      Amendment  appropriately executed ."
    	xlsheet.Cells(37,1).Value = "AUD letter"	
    	xlsheet.Cells(37, 1).font.bold = True
    	xlsheet.Cells(38,1).Value = "      AUD appropriately executed ."
    	xlsheet.Cells(39,1).Value = "Reinsurance"	
    	xlsheet.Cells(39, 1).font.bold = True
    	xlsheet.Cells(40,1).Value = "      Case coded appropriately for reinsurance ceding or retention."	
    	xlsheet.Cells(41,1).Value = "      Prior reinsured policy was not referred to Reinsurance Unit for review."
    	xlsheet.Cells(42,1).Value = "      Autobound Inappropriately."
    	xlsheet.Cells(43,1).Value = "MIB Coding"	
    	xlsheet.Cells(43, 1).font.bold = True
    	xlsheet.Cells(44,1).Value = "      MIB Coding appropriately executed."
    	xlsheet.Cells(45,1).Value = "Misc"	
    	xlsheet.Cells(45, 1).font.bold = True
    	xlsheet.Cells(46,1).Value = "      Benefits not included or deleted as necessary.  Policy issued and dated incorrectly (COD-forward dating, save age, specific issue date requests), incorrect plan, face, sex, dob, etc.   ."
    	xlsheet.Cells(47,1).Value = "      Illustration, certification or computer screen certification not submitted with app (when required)."
    	xlsheet.Cells(48,1).Value = "      New or Revised illustration not requested at issue. ."
    	xlsheet.Cells(49,1).Value = "      All other issues not mentioned above."
    	xlsheet.Cells(1,2).Value = "Counts (if Y)"
    	xlsheet.Cells(1, 2).font.bold = True
    		'set up counts
    'Start Import from Notes on this col
    	Set doc=dc.GetFirstDocument
    	While Not (doc Is Nothing)
    'fill cells with values from Notes and sets column widths
    		xlsheet.Cells(2, columns%).Value=doc.AnalystName(0)
    		xlsheet.Cells(3, columns%).Value=doc. CaseNumber(0)
    		xlsheet.Cells(4, columns%).Value=doc. FaceAmount(0)
    		xlsheet.Cells(5, columns%).Value=doc. Age(0)
    		xlsheet.Cells(6, columns%).Value=doc. ClientLastName(0)
    		xlsheet.Cells(7, columns%).Value=doc. Site(0)
    		xlsheet.Cells(8, columns%).Value=doc. Comment1(0)
    		xlsheet.Cells(11, columns%).Value=doc. ChBx1_9(0)
    		xlsheet.Cells(12,columns%).Value=doc. Rating_1(0)
    		xlsheet.Cells(11,2).Value = "=countif(C11:Z11,""Y"")"
    		xlsheet.Cells(13,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(15,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(17,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(18,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(20,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(23,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(25,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(28,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(30,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(31,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(34,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(41,2).Value = "=counta(C11:Z11)"
    		xlsheet.Cells(42,2).Value = "=counta(C11:Z11)"
    		xlSheet.Cells(8,columns%).Wraptext = True
    		xlSheet.Cells(8,columns%).Font.Size = 8	
    		xlSheet.Cells(8, columns%).Columnwidth = 30
    		xlSheet.Cells(13,columns%).Wraptext = True
    		xlSheet.Cells(13,columns%).Font.Size = 8	
    		xlSheet.Cells(49,columns%).Wraptext = True
    		xlSheet.Cells(49,columns%).Font.Size = 8	
    		Set doc=dc.GetNextDocument(doc)
    	xlSheet.Columns(1).Columnwidth = 40
    	xlSheet.Columns(1).Font.Size = 8	
    	xlSheet.Columns(2).Columnwidth = 15	
    	xlSheet.Columns(2).VerticalAlignment = 3
    	xlSheet.Columns(1).Wraptext = True     
    	xlsheet.Columns(1).HorizontalAlignment = 1
    ' Other formats
    ' Left Justify Analyst name
    '	xlsheet.Cells(1,2).HorizontalAlignment = 1
    ' Freeze panes
    	xlApp.application.ActiveWindow.FreezePanes = True
     ' Setting first 2 rows as header row to reprint on each page
    	xlsheet.PageSetup.PrintTitleRows = "$1:$2"
    ' Set the print to landscape, letter and 85% size
    	xlSheet.PageSetup.Orientation = 2
    	xlSheet.PageSetup.PaperSize = 1
    	xlSheet.PageSetup.Zoom = 85
    ' Headers and Automatic page number setup
    	xlSheet.PageSetup.CenterHeader = "Audit Summary"
    	xlSheet.PageSetup.CenterFooter = "Page &P of &N"
    	xlSheet.PageSetup.LeftFooter = "&D"
    ' Set the Printgridlines	& Page setup
    	xlSheet.PageSetup.PrintGridlines = True
    	xlSheet.PageSetup.LeftMargin = xlapp.InchesToPoints(0.81)
    	xlSheet.PageSetup.RightMargin = xlapp.InchesToPoints(0)
    	xlSheet.PageSetup.HeaderMargin = xlapp.InchesToPoints(0.25)
    	xlSheet.PageSetup.FooterMargin = xlapp.InchesToPoints(0)
    	xlSheet.PageSetup.TopMargin = xlapp.InchesToPoints(0.5)
    	xlSheet.PageSetup.BottomMargin = xlapp.InchesToPoints(0)
    	'Call xlsheet.saveas("C:\Export.xls") 
    	Set xlapp=Nothing
    End Sub

    Open in new window

    LVL 3

    Expert Comment

    Have you tried this one?
    xlsheet.Cells(11,2).Formula= "=COUNTIF(C11:Z11,""Y"")"

    This is how it works in Excel. I have no experience with lotus.
    So I guesse my help stops about here. I hope you can solve it.

    LVL 46

    Expert Comment

    by:Sjef Bosman
    And this:

    xlsheet.Cells(11,2).Value = |=(countif(c3:c13),"Y")|
    LVL 46

    Expert Comment

    by:Sjef Bosman
    there are three sets of string delimiters in LotusScript, examples: "string", |string| and {string}

    Author Comment

    Thanks - It works inserting the formulas, but when it hits the loop to pull in the documents

    'Start Import from Notes on this col
          Set doc=dc.GetFirstDocument
          While Not (doc Is Nothing)
    'fill cells with values from Notes and sets column widths
                xlsheet.Cells(2, columns%).Value=doc.AnalystName(0)
                xlsheet.Cells(3, columns%).Value=doc. CaseNumber(0)

    It stops with the error: OLE: Automation object error

    Is this error caused by my particular PC settings or is it a problem with the code?
    LVL 46

    Accepted Solution

    Can you please add some error trapping code, or run the code using the debugger, so you can better pinpoint the line where execution stops.

    For error trapping use:

                On Error Goto oops

    And at the end of the code:

                Exit Sub
                Print Error$ " on line " Erl
                Resume exitsub

    Assisted Solution

    Thanks!  It was on the line where I tried the vertical bar

    xlsheet.Cells(11,2).Value = |=(countif(C11:Z11),"Y")|

    I changed it back to quotes and it works like a charm.  I will finish testing and then come back and award the points (In case something else goes wrong)  - but I have learned a valuable lesson on error handling :)

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for jkee54's comment http:/Q_27389708.html#36948419

    for the following reason:

    Sorry I was late closing this - thanks again!

    Author Comment

    sorry - I clicked the wrong Accept button - I meant to accept the previous by sjef.  Please cancel the acceptance of my own comment and award the points to sjef bosman, thanks

    Author Closing Comment

    I accepted my own as a mistake - all points to sjef.. thanks!
    LVL 46

    Expert Comment

    by:Sjef Bosman
    It's very much appreciated :-)

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
    Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now