Slow Procedure

[Guest]

Hi all,
I have this code to grab the first 3 chars from a line. Problem is.. whenever its running, the cursor quickly switches from the normal arrow to hourglass, giving it a flickering effect. Any ways to eliminate that?? Also, I have already set screenUpdating = False.


Sub GetSVCodeFromLine16()

Dim objLastCell As Object, cell As Object, strSVCode As String

Set objLastCell = RealLastCell(Application.ActiveSheet)

For Each cell In Range("S2", objLastCell)
strSVCode = Left(Trim(cell.Value), 3)
cell.Offset(rowOffset:=0, columnOffset:=1).FormulaR1C1 = UCase(strSVCode)
Next cell

End Sub

 

[Dave Hawley]

No too sure if your last cell is in Column "S", but if so use:

Sub GetSVCodeFromLine16()

Dim objLastCell As Range, cell As Range

Range("S2", Range("S65536").End(xlUp)) _
.FormulaR1C1 = "=UPPER(LEFT(TRIM(RC[-1]),3))"

Range("S2", Range("S65536").End(xlUp)) = _
Range("S2", Range("S65536").End(xlUp)).Value

End Sub

Loops ae generally very slow in comparison.

Not to sure what method you are using to find the real last cell either but the safets way is via the Find Method. I have many examples here: http://www.ozgrid.com/VBA/ExcelRanges.htm