Left loop

[wibbers2000]

I have a problem with the left in a loop.

I can code it into the sheet with =left(j2,5) and that works fine and I can drag the formula down the page. However I am struggling to get it to work via VBA in a loop.

Ideally what I would like is to look at column D and if it is not blank "" then look at column J and take the first 5 digits and put them into column O

Any assistance would be greatly received.

regards
Paul

[RichardSchollar]

Hi Paul

How about:

Sub Test()
Dim c As Range
With Sheets("Sheet1")   'amend as appropriate
    For Each c In .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row) 'assumed header in row 1
        If Not IsEmpty(c.Value) Then
            c.Offset(, 11).Value = Left$(c.Offset(, 6).Text, 5)
        End If
    Next c
End With
End Sub

Best regards

Richard

[mdmackillop]

An alternative

 
Sub Test2()
Dim c As Range, tmp As Range
    Set tmp = Selection
    Set c = Sheets("Sheet3").Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))
    With c
    .AutoFilter Field:=1, Criteria1:="<>"
    .Offset(, 11).FormulaR1C1 = "=Left(RC[-5],5)"
    .AutoFilter
    .Offset(, 11).Copy
    .Offset(, 11).PasteSpecial xlValues
    End With
    Application.CutCopyMode = False
    tmp.Select
End Sub

[wibbers2000]

Thank you... I tried this first solution and it worked perfect.