Change the number after decimal

0

vba is placed in a seperate file

my file name  is 5.csv and it doesnt contains any headers

we have to open the file

all files are located in same place

if  column L , if second number after decimal is not 5 and above 5 then make it to 5 or if it is not 5 & 0 & it is below 5 then make it to 0

example if the data in olumn L is

10.76 then change it to 10.75

10.74 then change it to 10.70

save the changes and close the file

Answer
Discuss

Answers

0
Selected Answer

You can use the formula below to round the numbers you have in column L. Copy it to row 2, in any column of your worksheet and copy down as required.

=ROUNDDOWN((L2*2),1)/2

If you are looking for a VBA solution, the procedure below will appply the same formua to all values in column L.

Sub RoundDown_ColumnL()

    Dim Tmp As Variant
    Dim Rl As Long
    Dim R As Long

    ' Better: replace ActiveSheet with Worksheets("[Tab name]")
    With ActiveSheet
        Rl = .Cells(.Rows.Count, "L").End(xlUp).Row
        ' presuming that the first value to round is in row 2
        For R = 2 To Rl
            With .Cells(R, "L")
                Tmp = .Value
                If IsNumeric(Tmp) Then      ' skip if value isn't a number
                    .Value = Application.WorksheetFunction.RoundDown(Tmp * 2, 1) / 2
                End If
            End With
        Next R
    End With
End Sub

You might embed the above in a larger program that opens and closes a particular workbook. As you correctly point out, such code could be contained in another file, perhaps an add-in, which would require still further managment. That you would have to set up first. If you have questions about that they are unrelated to this thread and can't, therefore, be dealt with here.

Discuss

Discussion

Sub RoundDown_ColumnL()
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim Tmp As Variant
    Dim Rl As Long
    Dim R As Long
 
    Application.ScreenUpdating = False
 
    Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\BasketOrder..csv")
    Set wsh1 = wbk1.Worksheets(1)
    With wsh1
        Rl = .Cells(.Rows.Count, "L").End(xlUp).Row
        ' presuming that the first value to round is in row 2
        For R = 1 To Rl
            With .Cells(R, "L")
                Tmp = .Value
                If IsNumeric(Tmp) Then      ' skip if value isn't a number
                    .Value = Application.WorksheetFunction.RoundDown(Tmp * 2, 1) / 2
                End If
            End With
        Next R
    End With
    Application.DisplayAlerts = False
    wbk1.Close SaveChanges:=True
    Application.DisplayAlerts = True
    
    Application.DisplayAlerts = True
End Sub




i have changesd the code as per my condition plz recheck Sir and let me know wheather it contains any error or any mistake
Problem has been solved ur answer is perfect only i have changesd the same according to my condition so plz recheck the code and let me know wheather i have maken any mistake in the same
rider1234 (rep: 4) Jul 14, '19 at 1:13 am
Hello rider, That looks admirable. Just "\BasketOrder..csv" would probably work better with a single period. Suppression of application alerts shouldn't be required because an alert would only be issued if SaveChanges isn't specified, which wbk1.Close SaveChanges:=True avoids. Of course, the final line in your code is a duplicate. You may have intended to re-enable ScreenUpdating there but I think VBA does that automatically at the end of the procedure.
Variatus (rep: 3368) Jul 14, '19 at 2:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login