VBA: 400 Error on For Each Loop

[kajhvlksjdnf]

Greetings,

I've been trying to figure out why a certain piece of code crashes when I change the range past column Z. Any suggestions would be greatly appreciated.

----------------------------


Const FirstColumn As String = "AE"

totalCounter = 0

' For some reason a 400 error occurs when going above a range past Z
For Each cell In Range("B2:Z2") ' This works
' For Each cell In Range("B2:AC2") ' <--- THIS BREAKS and gives 400 ERROR

currentCell = cell.Address
activeColumn = Replace(Left(currentCell, 2), "$", "")
activeRow = Replace(Right(currentCell, 2), "$", "")

If cell.Value = 1 And Range(activeColumn & activeRow).Offset(0, -1) <> 1 Then

Range(FirstColumn & activeRow).Offset(0, totalCounter).Value = Range(activeColumn & 1).Value

' Make START time Bold
Range(FirstColumn & activeRow).Offset(0, totalCounter).Font.Bold = True

totalCounter = totalCounter + 1

ElseIf cell.Value = 1 And Range(activeColumn & activeRow).Offset(0, 1) <> 1 Then

Range(FirstColumn & activeRow).Offset(0, totalCounter).Value = Range(activeColumn & 1).Offset(0, 1).Value

totalCounter = totalCounter + 1

End If

Next

[mdmackillop]

activeColumn = Split(cell.Address, "$")(1)
activeRow = cell.Row

Always use Option Explicit and declare your variables
Consider using Cells(row,column) rather than Range(Column & Row). It's much more easily manipulated.
In your code activeRow is always 2, so no need to determine it.
Personally, I find Cell as a variable confusing!

Try

Sub Test()
Dim Rw As Long, Col As Long
Dim TotalCounter As Long
Dim cel As Range
Const FirstColumn As Long = 31
For Each cel In Range("B2:AE2")
Col = cel.Column
Rw = cel.Row
With Cells(Rw, FirstColumn).Offset(0, TotalCounter)
If cel.Value = 1 And Cel.Offset(0, -1) <> 1 Then
.Value = Cells(1, Col).Value
.Font.Bold = True
TotalCounter = TotalCounter + 1
ElseIf cel.Value = 1 And cel.Offset(0, 1) <> 1 Then
.Value = Cells(1, Col).Offset(0, 1).Value
TotalCounter = TotalCounter + 1
End If
End With
Next
End Sub

[kajhvlksjdnf]

Outstanding forum and thank you for actually modifying the entire code to a more compact version that works! The code doesn't generate any errors and I did intend to check other rows, so I ended up resetting the totalCounter so it could start over on the next row. Thanks!

--------------------------------


Sub Test()
Dim Rw As Long, Col As Long
Dim TotalCounter As Long
Dim cel As Range
Const FirstColumn As Long = 31
For Each cel In Range("B2:AC4")
Col = cel.Column
Rw = cel.Row
With Cells(Rw, FirstColumn).Offset(0, TotalCounter)
If cel.Value = 1 And cel.Offset(0, -1) <> 1 Then
.Value = Cells(1, Col).Value
.Font.Bold = True
TotalCounter = TotalCounter + 1
ElseIf cel.Value = 1 And cel.Offset(0, 1) <> 1 Then
.Value = Cells(1, Col).Offset(0, 1).Value
TotalCounter = TotalCounter + 1
End If
End With
If Col = 29 Then
TotalCounter = 0
End If
Next
End Sub