Help modifying macro - accounting analysis

[Shultzie]

I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Dim Arrayone(0 To 9) As Integer
Dim Arraytwo(0 To 9) As Integer
Dim Arraythree(0 To 9) As Integer
Dim Arrayfour(0 To 9) As Integer
Dim Arrayfive(0 To 9) As Integer
Dim Arraysix(0 To 9) As Integer
Dim Arrayseven(0 To 9) As Integer
Dim Arrayeight(0 To 9) As Integer
Dim Arraynine(0 To 9) As Integer
Dim Arrayzero(0 To 9) As Integer
Dim Arraytwotest(10 To 99) As Integer

Dim x, I

Dim Row As Long, Col As Long, Step As Long, Colcells

Dim Digits As Long, Total As Long

Col = Application.CountA(ActiveSheet.Range("1:1"))

For Step = 1 To Col
Cells(1, Step).Select
Selection.End(xlDown).Select
Row = ActiveCell.Row
For Colcells = 1 To Row

x = Left(Cells(Colcells, Step), 2)
If x > 9 Then
Arraytwotest(x) = Arraytwotest(x) + 1
End If


For Digits = 1 To Len(Cells(Colcells, Step))

Select Case Mid(Cells(Colcells, Step), Digits, 1)
Case 1
Arrayone(Digits) = Arrayone(Digits) + 1

Case 2
Arraytwo(Digits) = Arraytwo(Digits) + 1

Case 3
Arraythree(Digits) = Arraythree(Digits) + 1

Case 4
Arrayfour(Digits) = Arrayfour(Digits) + 1

Case 5
Arrayfive(Digits) = Arrayfive(Digits) + 1

Case 6
Arraysix(Digits) = Arraysix(Digits) + 1

Case 7
Arrayseven(Digits) = Arrayseven(Digits) + 1

Case 8
Arrayeight(Digits) = Arrayeight(Digits) + 1

Case 9
Arraynine(Digits) = Arraynine(Digits) + 1

Case 0
Arrayzero(Digits) = Arrayzero(Digits) + 1

End Select

Next Digits

Next Colcells
Next Step



Worksheets(2).Range("C5").Value = Arrayone(1)
Worksheets(2).Range("C6").Value = Arraytwo(1)
Worksheets(2).Range("C7").Value = Arraythree(1)
Worksheets(2).Range("C8").Value = Arrayfour(1)
Worksheets(2).Range("C9").Value = Arrayfive(1)
Worksheets(2).Range("C10").Value = Arraysix(1)
Worksheets(2).Range("C11").Value = Arrayseven(1)
Worksheets(2).Range("C12").Value = Arrayeight(1)
Worksheets(2).Range("C13").Value = Arraynine(1)
Worksheets(3).Range("C5").Value = Arrayzero(2)
Worksheets(3).Range("C6").Value = Arrayone(2)
Worksheets(3).Range("C7").Value = Arraytwo(2)
Worksheets(3).Range("C8").Value = Arraythree(2)
Worksheets(3).Range("C9").Value = Arrayfour(2)
Worksheets(3).Range("C10").Value = Arrayfive(2)
Worksheets(3).Range("C11").Value = Arraysix(2)
Worksheets(3).Range("C12").Value = Arrayseven(2)
Worksheets(3).Range("C13").Value = Arrayeight(2)
Worksheets(3).Range("C14").Value = Arraynine(2)
Worksheets(4).Select
Range("d3").Select
For I = 10 To 99
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Arraytwotest(I)
Next I

Worksheets(2).Select
End Sub

 

[Weaver]

I don't think it's relevant to what you're trying to do anyway. Sure a multidimensional array (sounds so 'sci-fi' doesn't it?) will do the same job, but you're not trying to optimise the code, just make it do something slightly different.

Bearing that in mind, could you have a look at my query above, re the type mismatch error?

 

[Shultzie]

I'm not sure what's causing the error. Were you able to look at that spreadsheet in the link I posted? If it's easier, the second column of data can be deleted, as my data set only has 1 column of relevant data.

I wish I knew more about this stuff, so I could be of more help - I plan on taking a learning course this year...just was hoping to find a quick solution here so I could have the spreadsheet ready for Monday.

I appreciate you helping me out though.

 

[Weaver]

I think it's the pennies in the data in cell A10. So you're saying this worked fine before?

Don't worry about getting something working - it's probably a really easy fix.

 

[Weaver]

I'm assuming you understand the principle behind the analysis method, even if you don't 'get' the code, by the way.

 

[Shultzie]

Yes, it worked perfectly before.

 

[Shultzie]

I do understand the principle - by profession I'm an auditor and use this in my analysis of recorded transactions (looking for fraud). I have a spreadsheet I created that performs various statistical analysis on populations of transactional data, and just want to be able to incorporate this into that workbook.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Weaver]

On exactly that data?

Doesn't much like that $176.38 on row 43 either

 

[Weaver]

I do understand the principle - by profession I'm an auditor and use this in my analysis of recorded transactions (looking for fraud). I have a spreadsheet I created that performs various statistical analysis on populations of transactional data, and just want to be able to incorporate this into that workbook.<o></o>

I only asked because I don't!

I just wondered why it went through each number 1 character at a time.

 

[Shultzie]

The data changes all the time - this data is just example data provided with the spreadsheet. In my case, the data will always be financial data associated with transactions entered into by a company (i.e., journal entries recorded).

 

[Weaver]

Should it be ignoring the decimal point character, maybe?