You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
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
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
--------------------------------------
I have a not-so-related question regarding nested for each cell to ask (as not being able to open a new post). I am running a project, part of the code is like this:
Code:
For Each cell In Range("i17:i" & x)
If cell.Value > 0 Then
bp = cell.Offset(0, -2)
r = cell.Row
For Each cell In Range(Cells(r + 1, 7), Cells(x, 7))
t = t + cell.Value - bp
If t >= bp * 0.15 Or t <= bp * (-0.1) Then
Range("m1") = t
Exit For
End If
Next
Next cell
Range("n1") = t
I know the problem lies with the 2 nested "For Each cell" clauses, but just couldn't solve it. Can anyone please help?
cell is empty while using For Each cell In Range
Linear Mode
