Other - Example 5

Sub Example5()
'
' Extract data to a worksheet all claims
' between $10 and $20
' file is read and then
' the file totals are shown on the Excel status bar
'
Dim ofile As New cNFile
Dim sMsg As String
Dim sText As String
Dim dTotals As Double
Dim icol As Integer
Dim sTextName As String
Dim iPaidAmountColumn As Integer
Dim iProviderNumberColumn As Integer
Dim sFormattedNumber As String
Dim dClaimAmount As Double
Dim r As Range
Dim sSheet As String
Dim c As Object
Dim i As Integer

Application.Cursor = xlWait
ofile.Filename = BASEDIR & "clmdenbp.txt"
sTextName = "paidamt"
sSheet = "Selected Claims"
CheckSheet (sSheet)
Set r = Sheets(sSheet).UsedRange
r.Clear
Set c = Sheets(sSheet).Range("a1")

Call ofile.OpenFile
iPaidAmountColumn = ofile.getcolno("paidamt")
iProviderNumberColumn = ofile.getcolno("billprov")
' write column headers
For i = 0 To ofile.numcols
c.Offset(0, i).Value = ofile.HdrCols(i)
Next i
' advance to next worksheet row
Set c = c.Offset(1, 0)
'
'Read the claim file and total all claims between $10 and $0
'
Do While ofile.EOF = False
Call ofile.GetRow
sText = ofile.getcol(iPaidAmountColumn)
If IsNumeric(sText) Then
dClaimAmount = CDbl(sText)
If dClaimAmount >= 10 And dClaimAmount <= 20 Then
dTotals = dTotals + CDbl(sText)
For i = 0 To ofile.numcols
c.Offset(0, i).Value = ofile.getcol(i)
Next i
Set c = c.Offset(1, 0)
End If
End If
Loop
Set ofile = Nothing
sFormattedNumber = Format(dTotals, "###,###,###.00")
Application.StatusBar = "Ex5: File totals for " & sTextName & ": " & sFormattedNumber
Application.Cursor = xlDefault
End Sub

Main Page

Complete Auditor resources
Links \| Privacy Policy \| FAQ \| Downloads \| Trademarks

	Other - Example 5
	Benford's Law Downloads Site Map