PDA

View Full Version : [SOLVED] Left loop



wibbers2000
08-26-2007, 11:37 PM
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
08-27-2007, 12:10 AM
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
08-27-2007, 09:56 AM
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
08-27-2007, 01:25 PM
Thank you... I tried this first solution and it worked perfect.