Format Data Type of Selected Cells in a Loop
I'm trying to change the data type of the selected cells using VBA and my current code is not working. I'm looping through multiple Excel files in a directory but it seems like the current code either doesn't open the workbook or save the change in the end.
I want to open each Excel file, change the data type of the two selected cell, and save the change. Here's the code I have now:
Sub Formatting()
Dim MyFolder As String, MyFile As String
MyFolder = "C:\Users\Documents\Files"
Application.ScreenUpdating = False
MyFile = Dir(MyFolder)
Do While MyFile <> ""
Workbooks.Open fileName:=MyFolder & "\" & MyFile
Range("G4").NumberFormat = "0.00"
Range("E4").NumberFormat = "m/d/yyyy"
Workbooks(MyFile).Save
MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub
Save the workbook object into a variable, and then use that with the ActiveSheet.Range
.
Dim wb As Workbook
Do While MyFile <> ""
Debug.Print MyFolder & "\" & MyFile
Set wb = Workbooks.Open(FileName:=MyFolder & "\" & MyFile)
wb.Activesheet.Range("G4").NumberFormat = "0.00"
wb.Activesheet.Range("E4").NumberFormat = "m/d/yyyy"
wb.Close SaveChanges:=True
MyFile = Dir
Loop
Note: if there is more than one sheet, whichever one is active will get the change. If there is a better way to identify the sheet, then use that instead of ActiveSheet.