none
Excel VBA Evaluate Function (Relative vs. Absolute) RRS feed

  • Question

  • I have a column of numbers that I need to truncate using VBA. All the numbers have a minimum of 8 digits and I want to keep the first 7 digits on the left.

    I can loop through the column but have recently discovered the power of EVALUATE. It has saved me a lot of waiting around for loops to finish as it's virtually instant.

    In this case, however, I can't get it to work. Take the following column, for example:

    12345678

    23456789

    987654321

    89123456789

    I tried using the following code,

    Sub TruncateTest()
        With Range("A1", Range("A" & Rows.Count).End(xlUp))
            .Value = Evaluate("=LEFT(" & .Address & ",7)")
        End With
    End Sub

    but the column now reads:

    1234567

    1234567

    1234567

    1234567

    Only the first cell seems to be getting evaluated but then applied to all cells. Is there something in the code that is keeping the reference absolute? I tried .Address(0,0) but without success. I use similar syntax for concatenating and it works fine. Where am I going wrong?

    Thanks in advance,

    SQL Servant

    Tuesday, August 21, 2012 12:33 PM

Answers

  • You need to loop through the cells of the range.

    And note that Evaluate is unneeded, since LEFT is a VBA function already. Evaluate is needed for when you want to use a string representation of a worksheet function that is written out rather than passed ranges. Usually SUMPRODUCT - (see http://answers.microsoft.com/en-us/office/forum/office_2007-excel/vba-sumproduct/7ffca54d-48c9-4bdf-ab2c-97ba278fa0f0)

    Sub TruncateTest2()
    Dim rngC As Range

    For Each rngC In Range("A1", Range("A" & Rows.Count).End(xlUp))
           rngC.Value = Left(CStr(rngC.Value), 7)
    Next rngC

    End Sub


    Tuesday, August 21, 2012 1:09 PM
  • I don't see why you need the Evaluate here.  Rather, this worked for me ...

    Sub test()
    If Range("A1") = "" Then Range("A1").End(xlDown).Select
    Row = Selection.Row
    With ActiveSheet
      Do Until .Cells(Row, 1).Value = ""
        .Cells(Row, 1).Value = Left(.Cells(Row, 1).Value, 7)
        Row = Row + 1
      Loop
    End With
    End Sub


    Tom Lavedas

    • Marked as answer by SQL Servant Tuesday, August 21, 2012 2:12 PM
    Tuesday, August 21, 2012 1:09 PM

All replies

  • You need to loop through the cells of the range.

    And note that Evaluate is unneeded, since LEFT is a VBA function already. Evaluate is needed for when you want to use a string representation of a worksheet function that is written out rather than passed ranges. Usually SUMPRODUCT - (see http://answers.microsoft.com/en-us/office/forum/office_2007-excel/vba-sumproduct/7ffca54d-48c9-4bdf-ab2c-97ba278fa0f0)

    Sub TruncateTest2()
    Dim rngC As Range

    For Each rngC In Range("A1", Range("A" & Rows.Count).End(xlUp))
           rngC.Value = Left(CStr(rngC.Value), 7)
    Next rngC

    End Sub


    Tuesday, August 21, 2012 1:09 PM
  • I don't see why you need the Evaluate here.  Rather, this worked for me ...

    Sub test()
    If Range("A1") = "" Then Range("A1").End(xlDown).Select
    Row = Selection.Row
    With ActiveSheet
      Do Until .Cells(Row, 1).Value = ""
        .Cells(Row, 1).Value = Left(.Cells(Row, 1).Value, 7)
        Row = Row + 1
      Loop
    End With
    End Sub


    Tom Lavedas

    • Marked as answer by SQL Servant Tuesday, August 21, 2012 2:12 PM
    Tuesday, August 21, 2012 1:09 PM
  • Ok I think I'll use this approach. I was hoping the evaluate function would work because it works very well in other instances (e.g. concatenation) but I just tried your and Tom's routines and they were much quicker than I thought.

    Thanks,

    SQL Servant

    Tuesday, August 21, 2012 2:10 PM
  • You don't need it for concatenation, either. Simply using & works better.
    Tuesday, August 21, 2012 2:35 PM