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

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.

SUM, COUNT, and COUNTA all work, but COUNTIF DOESN'T.

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?
jkee54Asked:
Who is Participating?
 
Sjef BosmanGroupware ConsultantCommented:
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:

      exitsub:
            Exit Sub
      oops:
            Print Error$ " on line " Erl
            Resume exitsub
0
 
Haris DulicCommented:
try this:

xlsheet.Cells(11,2).Value = "=countif(c3:c13;"Y")"
0
 
jkee54Author Commented:
Still gives the same error.  Seems like it doesn't recognize COUNTIF
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Davy2270Commented:
try this:

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

Davy
0
 
Davy2270Commented:
or better:

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

Just saw that your separator is a comma instead of ;

Davy
0
 
jkee54Author Commented:
Davy,

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".
0
 
jkee54Author Commented:
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.  
0
 
Davy2270Commented:
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?
0
 
jkee54Author Commented:
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")
	xlApp.visible=True
	xlApp.Workbooks.Add
	Set xlsheet=xlApp.Workbooks(1).Worksheets(1)
	
'Load column and row headers
	
	
' Row titles
'xlsheet.cells(row,colum)
	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
	columns%=3
	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(13,columns%).Value=doc.Comment2(0)
		xlsheet.Cells(15,columns%).Value=doc.ChBx1_1(0)
		xlsheet.Cells(17,columns%).Value=doc.ChBx1_2(0)
		xlsheet.Cells(18,columns%).Value=doc.ChBx1_2_1(0)
		xlsheet.Cells(20,columns%).Value=doc.ChBx1_3(0)
		xlsheet.Cells(23,columns%).Value=doc.ChBx1_4(0)
		xlsheet.Cells(25,columns%).Value=doc.ChBx1_6(0)
		xlsheet.Cells(28,columns%).Value=doc.ChBx1_7(0)
		xlsheet.Cells(30,columns%).Value=doc.ChBx1_8(0)
		xlsheet.Cells(31,columns%).Value=doc.ChBx1_8_1(0)
		xlsheet.Cells(33,columns%).Value=doc.ChBx1_10(0)
		xlsheet.Cells(34,columns%).Value=doc.ChBx1_11(0)
		xlsheet.Cells(36,columns%).Value=doc.ChBx1_13(0)
		xlsheet.Cells(38,columns%).Value=doc.ChBx1_14(0)
		xlsheet.Cells(40,columns%).Value=doc.ChBx1_15(0)
		xlsheet.Cells(41,columns%).Value=doc.ChBx1_16(0)
		xlsheet.Cells(42,columns%).Value=doc.ChBx1_16_1(0)
		xlsheet.Cells(44,columns%).Value=doc.ChBx1_18(0)
		xlsheet.Cells(46,columns%).Value=doc.ChBx1_19(0)
		xlsheet.Cells(47,columns%).Value=doc.ChBx1_20(0)
		xlsheet.Cells(48,columns%).Value=doc.ChBx1_21(0)
		xlsheet.Cells(49,columns%).Value=doc.Comment1_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)
		columns%=columns%+1
		
	Wend
	
'FORMATS
	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.Cells(3,2).Select
	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") 
	'XLApp.ActiveWorkBook.Close
	'XLApp.Quit
	Set xlapp=Nothing
End Sub

Open in new window

0
 
Davy2270Commented:
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.

Davy
0
 
Sjef BosmanGroupware ConsultantCommented:
And this:

xlsheet.Cells(11,2).Value = |=(countif(c3:c13),"Y")|
0
 
Sjef BosmanGroupware ConsultantCommented:
Explanation:
there are three sets of string delimiters in LotusScript, examples: "string", |string| and {string}
0
 
jkee54Author Commented:
Thanks - It works inserting the formulas, but when it hits the loop to pull in the documents

'Start Import from Notes on this col
      columns%=3
      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)
...etc....


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?
0
 
jkee54Author Commented:
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 :)
0
 
jkee54Author Commented:
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!
0
 
jkee54Author Commented:
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
0
 
jkee54Author Commented:
I accepted my own as a mistake - all points to sjef.. thanks!
0
 
Sjef BosmanGroupware ConsultantCommented:
It's very much appreciated :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.