Results 1 to 4 of 4

VBA to truncate entry data in Excel does not work on some numbers!

Threaded View

  1. #1
    Registered User
    Join Date
    07-24-2023
    Location
    Los Angeles
    MS-Off Ver
    Mac
    Posts
    1

    Question VBA to truncate entry data in Excel does not work on some numbers!

    I use this piece VBA code to truncate the input numbers in Excel but in some cases it still round the number rather than truncating them. When I try some specific numbers, it does NOT work. The examples I found are: 1.11116, 17.84116.

    I use this piece VBA code to truncate the input numbers in Excel but in some cases it still round the number rather than truncating them. When I try some specific numbers, it does NOT work. The examples I found are: 1.11116, 17.84116.

    These are the only numbers I came across that the code does not work on. Both end with a 6, but there is no pattern to the issue.

    This is the code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Const TARGET_RANGE As String = "A1:A10"
    Const DECIMAL_PLACES As Long = 5
    On Error GoTo ClearError
    Dim irg As Range: Set irg = Interesect(Me.Range(TARGET_RANGE),Target)
    If irg Is Nothing Then Exit Sub
    Dim Num As Long: Num = 10^DECIMAL_PLACES
    Application.EnableEvents = False
    Dim iCell As Range, iValue, dValue As Double
    For Each iCell In irg.Cells
      iValue=iCell.Value
     If VarType(iValue)=vbDouble Then
       dValue=Int(iValue * Num)/Num
       If dValue<iValue Then
        iCell.Value=dValue
       End If
     End If
    Next iCell
    ProcExit:
     On Error Resume Next
      If Not Application.EnbaleEvents Then Application.EnableEvents = True
     On Error GoTo O
     Exit Sub
    ClearError:
     Resume ProcExit
    End Sub
    Does anyone have any ideas why this is happening and how to fix it? Thanks.
    Last edited by AliGW; 07-25-2023 at 02:22 AM. Reason: Code tags added.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 04-12-2023, 11:28 AM
  2. [SOLVED] Data Entry from one excel workbook to several excel work sheets "Else without if" Error
    By rajtar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2016, 10:49 AM
  3. How do I import an XML file into Excel using Macros to ensure none of the data is truncate
    By kiran_rangarajan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2015, 11:06 AM
  4. How to truncate numbers on right side of alphabets
    By Tai Chi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2015, 08:25 PM
  5. Need A data entry form to save the data in several work sheets
    By nilesh_urkude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 12:25 AM
  6. Excel Worksheet Subtotals Data Truncate
    By tomwashere2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1