Search code examples
excelvbainfinite-looplowercase

Trying to understand the difference

I hope nobody will be offended by me asking a question about a working code. I was told, however, SO is also for learning so I hope this will be fine.

What I want is to try and understand the difference between two codes I wrote, one working, one failing.

This code is a failing version:

Sub Lowercase()
Dim Seq As Range
Set Seq = Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell))
    For Each cell In Seq
    cell.Value = LCase(cell.Value)
    Next cell
End Sub

It does put things in lowercase just fine, but it loops forever until I close or minimize Excel. Thinking on an older code I changed the code to this:

Sub Lowercase()
Dim Seq As Range, v As Variant
Dim L As Long, i As Long
Set Seq = Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell))
  For Each cell In Seq
      v = cell.Value
      L = Len(v)
      For i = 1 To L
          cell.Value = LCase(cell.Value)
      Next i
  Next cell
End Sub

This works fine (bit slow but okay) nor do I understand why.

The files I am trying to convert look like (The A2 row tends to go on for 600 to 1200 characters):

enter image description here

However, I still call all cells and I am not sure why giving the cell a value it actually stops looping. As, but I am sure I am wrong, I have the feeling I still did not change the "For each cell / next cell" part of the coding.

EDIT: It has to do with a faulty use of xlLastCell for my worksheets as that was not the last cell in Row 2. In the second code a value could hit 0, whereas in the first not. The code had to be altered to use a different method to find the last cell.

Solution

  • There seems to be some sort of weird interactions (between what, I don't know) going on with your spreadsheet. The following won't reveal what those interactions are but might suppress them. Also, your code has the effect of replacing any blank cells in the range by strings of length zero. I can't imagine that there is any point to this, so I through in a guard against it. I also grabbed the last used cell in Row2 in a more reliable way:

    Sub Lowercase()
    Dim Seq As Range, Cell As Range, LastCell As Range

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set LastCell = Cells(2, Columns.Count).End(xlToLeft) 'last cell in Row 2 with data, assuming that not all cells in row have data
    Set Seq = Range("A2", LastCell)
    For Each Cell In Seq
        If Len(Cell.Value) > 0 Then Cell.Value = LCase(Cell.Value)
    Next Cell

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub