left() in vba

[lezawang]

Hi
I wanted to do the code below without using for-loop, but it did not work. Is it possible to do it without For loop?
Thank you so much.

Sub myleft()
    'For x = 1 To 10
    'Cells(x, 2) = Left(Cells(x, 1), 2)
    'Next x
    '''''''
    Range("b1:b10") = Left(Range("a1:a10"), 2)
End Sub

 

[alansidman]

try this:

Sub foo()
Range("B1:B10").Formula2R1C1 = "=LEFT(RC[-1],2)"
End Sub

 

[JoeMo]

Another way:

Sub WithoutLoop()
  With Range("A1:A10")
    .Offset(0, 1).Value = Evaluate(Replace("if(@="""","""",trim(replace(trim(@),3,len(@),"""")))", "@", .Address))
  End With

End Sub

 

[Rick Rothstein]

Another way (this puts the values in the cells directly rather than formulas referring back to the previous column)...

[table="width: 500"]
[tr]
	[td]Sub GetLeftTwoCharacters()
  Range("A1:A10").TextToColumns Range("B1"), xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(2, 9))
End Sub[/td]
[/tr]
[/table]

 

[Marcelo Branco]

Maybe...

Sub myleft()
    With Range("B1:B10")
        .Formula = "=Left(A1,2)"
        .Value = .Value
    End With
End Sub

M.