I work in an accounting firm and for audits we sometimes get general ledger
documents in Excel that we then process to see if there are interesting
patterns, etc.

The problem is that most of the time, these are basically puked into Excel
in print formats by whatever accounting application the client is using.
This means that instead of each transaction having it's own row with all of
the information, some of the information might be in a simulated "header" or
"footer" and there might be random columns inserted willy-nilly to replicate
how it looks on paper.

To address this, I usually hack out a little custom function that takes the
transactions from the raw data and puts them in neat rows in another
worksheet. My issue is that these tend to be fairly slow and resource
intensive. When they run, Excel immediately jumps to 100% CPU usage and
processing around 3500 rows takes five minutes or sometimes more. I'm much
more comfortable with Access VBA than with Excel and therefore I get the
feeling that there is probably a lot of optimization that could done to the
code. I'd appreciate any ideas that could speed up this code.

Anyway, without further ado, here's an example:

Public Sub CleanUp(intMaxRow As Integer, AcctNumCol As Integer, AcctNameCol
As Integer, ParamArray Cols() As Variant)
'Walk down the RawData sheet and look for one of two patterns,
"Account*" or "??##"

'If "Account*" is found, then copy the Account Name and Number to string
variables
'these will be used later for copying into the CleanData sheet.

'If "??##" this row is a transaction, so start building a discontinuous
range using the
'columns specified in the Cols paramarray.

Const StartRow As Integer = 2

Dim wksRawData As Worksheet
Dim wksCleanData As Worksheet
Dim rngRawData As Range
Dim rngTemp As Range

Dim intRawRow As Integer
Dim intCleanRow As Integer
Dim strAcctNum As String
Dim strAcctName As String
Dim intColCnt As Integer

Application.ScreenUpdating = False

intCleanRow = StartRow

Set wksRawData = Application.Worksheets("RawData")
Set wksCleanData = Application.Worksheets("CleanData")

'Walk through the RawData sheet
For intRawRow = 1 To intMaxRow
If wksRawData.Cells(intRawRow, 1) Like "Account*" Then
strAcctNum = Trim(wksRawData.Cells(intRawRow, AcctNumCol))
strAcctName = Trim(wksRawData.Cells(intRawRow, AcctNameCol))
ElseIf wksRawData.Cells(intRawRow, 1) Like "??##" Then

'This row is a transaction, write in the account number and name
wksCleanData.Cells(intCleanRow, 1) = strAcctNum
wksCleanData.Cells(intCleanRow, 2) = strAcctName

'Initialize the beginning of the info range
Set rngRawData = wksRawData.Cells(intRawRow, Cols(0))

'Build the range with the columns we need
For intColCnt = 1 To UBound(Cols)
Set rngTemp = wksRawData.Cells(intRawRow, Cols(intColCnt))
Set rngRawData = Application.Union(rngRawData, rngTemp)
Next intColCnt

Set rngTemp = wksCleanData.Cells(intCleanRow, 3)

rngRawData.Copy rngTemp

intCleanRow = intCleanRow + 1
End If

Next intRawRow

'Release all objects
Set rngTemp = Nothing
Set rngRawData = Nothing
Set wksCleanData = Nothing
Set wksRawData = Nothing

Application.ScreenUpdating = True

End Sub



--
Posted via a free Usenet account from http://www.teranews.com