Code to Loop Through Columns & Perform Action

[LucasLondon]

Hi,

I have the code below that I adapted to loop through each column of data (Columns A to GR) to perform an a calculation based on the data in rows 2 to 31 of each column and place the calculated statistic in row 15 for each column of data.

The problem is the code runs fine for the first 26 columns of data (i.e. row 15 for columns A:Z get populated with a correctly calculated statistic but code stops running at column Z, and generates then generates the following run time error (1004): method range of object of global failed.

It seems the macro stops working beyond column z. I?ve been informed it relates to this part of the code:
1. b = Chr(a + 64)'new
2. c = b & "2:" & b & y 'starts at row 2

After column 27, there is no proper referencing for the following columns and so the macro fails. Apparantly that is because the code currently uses the ASCII character set to define the columns to loop through and it is undefined beyond 90 (the code for "Z") and so fails.

I'm really struggling with this. Can anyone suggest an alternative preferably simplier way to loop through the columns so that the macro works beyond column Z?

Thanks,

Lucas

Sub Test()
Dim i As Long
Dim LaggedResiduals() As Double
Dim ResidualDifferenceSquared() As Double
Dim SumNumerator As Double
Dim SumDenominator As Double
Dim residuals As Range
Dim a, x, y As Integer 'new /new bits to loop through columns
x = Cells(2, Columns.Count).End(xlToLeft).Column 'new/start at row 2, determines no of columns to calculate
For a = 1 To x 'new
y = Cells(Rows.Count, a).End(xlUp).Row 'new
b = Chr(a + 64) 'new
c = b & "2:" & b & y 'starts at row 2
SumNumerator = 0
SumDenominator = 0
Set residuals = Range(c) 'new / c is the durbin watson for each column
ReDim LaggedResiduals(1 To residuals.Rows.Count - 1)
ReDim ResidualDifferenceSquared(1 To residuals.Rows.Count - 1)
For i = 1 To residuals.Rows.Count - 1
LaggedResiduals(i) = residuals(i + 1).Value
ResidualDifferenceSquared(i) = (residuals(i).Value - LaggedResiduals(i)) ^ 2
SumNumerator = SumNumerator + ResidualDifferenceSquared(i)
'MsgBox LaggedResiduals(i) & " " & ResidualDifferenceSquared(i) & " " & SumNumerator
Next i
For i = 1 To residuals.Rows.Count
SumDenominator = SumDenominator + residuals(i).Value ^ 2
Next i
Durbin = SumDenominator / SumNumerator
Range(b & "15").Value = SumNumerator / SumDenominator 'new , returns result in this row
Next a 'new/calculate next column in the range
End Sub

[Bob Phillips]

[vba]

Sub Test()
Dim i As Long
Dim LaggedResiduals() As Double
Dim ResidualDifferenceSquared() As Double
Dim SumNumerator As Double
Dim SumDenominator As Double
Dim Durbin
Dim residuals As Range
Dim a As Long, b, c, x, y As Integer 'new /new bits to loop through columns
x = Cells(2, Columns.Count).End(xlToLeft).Column 'new/start at row 2, determines no of columns to calculate
For a = 1 To x 'new
y = Cells(Rows.Count, a).End(xlUp).Row 'new
b = ColumnLetter(a) 'new
c = b & "2:" & b & y 'starts at row 2
SumNumerator = 0
SumDenominator = 0
Set residuals = Range(c) 'new / c is the durbin watson for each column
ReDim LaggedResiduals(1 To residuals.Rows.Count - 1)
ReDim ResidualDifferenceSquared(1 To residuals.Rows.Count - 1)
For i = 1 To residuals.Rows.Count - 1
LaggedResiduals(i) = residuals(i + 1).Value
ResidualDifferenceSquared(i) = (residuals(i).Value - LaggedResiduals(i)) ^ 2
SumNumerator = SumNumerator + ResidualDifferenceSquared(i)
'MsgBox LaggedResiduals(i) & " " & ResidualDifferenceSquared(i) & " " & SumNumerator
Next i
For i = 1 To residuals.Rows.Count
SumDenominator = SumDenominator + residuals(i).Value ^ 2
Next i
Durbin = SumDenominator / SumNumerator
Range(b & "15").Value = SumNumerator / SumDenominator 'new , returns result in this row
'Next a 'new/calculate next column in the range
End Sub

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
[/vba]