200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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
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)/2If 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 SubYou 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.
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