I've got a function which check for a particular date and then format columns around it. It was previously working when I use it within a Macro and open the file I want to work on manually and manually execute the Macro. However, when I move the code to a button and try to have VBA to automatically open the files with a folder and execute the existing formating code, it's not working anymore. The problem exist within the function below. The variable "cell" appears to be empty for some reason? Can any offer any help?


Function FormatSplit(sDate As String, wb As Workbook, Optional splitLeft As Boolean)


For Each cell In Range(Range("a2"), Range("IV2").End(xlToLeft))

If Format(cell.Value, "dd/mm/yyyy") = sDate Then
 If splitLeft = True Then
     Range(cell, Cells(1, Columns.Count)).EntireColumn.Delete
    Else
     iCount = Range("b2", cell).Count
     Range("b2", Cells(1, iCount)).EntireColumn.Delete
 End If
 Exit For
End If

Next cell


End Function

Try some data validation to make sure the cell is in date format first.
If certain it looks like a date, the system may not be seeing it as a date for whatever reason.

IsDate will tell you if the value is a date or not. cDate will turn it into a date if it is.
Trim will remove padding around the date.

Does this help any?

If you want to change the cell to a date if possible, create a function to change the date using cDate that will return a date if a good date is passed in or false if not... something like the function below:
--------------------------------------
Private Function ToDate(sPassed As String) As Variant

  Dim dDateToReturn As Date
  ToDate = False
  On Error GoTo IsDate_False
    dDateToReturn = CDate(sPassed)
    ToDate = dDateToReturn
  On Error Resume Next
IsDate_False:

End Function
--------------------------------------