CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

vba left function doesnt work as planned

I am trying to get partial of the Account Code using left function but it seemed like it doesn't work the way I want to. All of my data are in worksheet named Codes. I have Account Name in Column B & Account Code in Column C, and I want to populate the parent code in column A.

Now the code works except when the codes are 0001-xx-xx, it populates in Column A as 1 not 0001. Is there a way to fix it so it shows 0001 or 0022 and such? When I typed out the formula in excel, it will show 0002 or 0022. So I would like to achieve this using vba.

Dim lLastRow As Long
With Sheets("Codes")
lLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
      With .Range("A5:A" & lLastRow)
         .FormulaR1C1 = "=LEFT(RC[2],4)"
         .Value = .Value
      End With
End With

Upvotes: 0

Views: 674

Answers (2)

J. Garth
J. Garth

Reputation: 803

To stick with your approach, you can add an apostrophe to the formula so that it will format the numbers as text and not remove the leading zeros:

Dim lLastRow As Long

With Sheets("Codes")
    lLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    With .Range("A5:A" & lLastRow)
       .FormulaR1C1 = "= ""'"" &LEFT(RC[2],4)"
       .Value = .Value
    End With
End With

Although the apostrophe won't be visible in the cell, it will still be there. If that causes problems, then you can use this approach:

Sub foo()

    Dim lLastRow    As Long
    Dim arrCodes()  As Variant
    Dim rng         As Range
    Dim i           As Integer

    With Sheets("Codes")
        lLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        Set rng = .Range("C5:C" & lLastRow)
        arrCodes = rng
        arrCodes = Application.Transpose(arrCodes)

        For i = LBound(arrCodes) To UBound(arrCodes)
            arrCodes(i) = Left(arrCodes(i), 4)
        Next i

        Set rng = .Range("A5")
        Set rng = rng.Resize(UBound(arrCodes), 1)
        rng.NumberFormat = "@"
        rng.Value = Application.Transpose(arrCodes)
    End With

End Sub

Upvotes: 1

Jeremy
Jeremy

Reputation: 1337

You need to change the format to text:

Dim lLastRow As Long
With Sheets("Codes")
lLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
      With .Range("A5:A" & lLastRow)
         .NumberFormat = "@"
         .FormulaR1C1 = "=LEFT(RC[2],4)"
         .Value = .Value
      End With
End With

Upvotes: 0

Related Questions