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
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