cell is empty while using For Each cell In Range

jase2007 · July 9th, 2008, 04:24 AM

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

allenm · July 11th, 2008, 04:56 PM

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
--------------------------------------

kang0024 · April 5th, 2012, 11:43 AM

Hi,

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?

Thanks a lot!

KH

Shasur · April 5th, 2012, 10:01 PM

Hi

It is tough to comprehend. The outer loop is for Column I and the inner one is for G ..

It is better if you can explain what you are trying to do .. this piece of code should be easy for you as you have writtent it

Cheers
Shasur

kang0024 · April 5th, 2012, 10:20 PM

What I am trying to do is to sum the accumulative differences at column G subject to certain constraints.

I think it doesn't matter, I am just asking if there is any way to avoid using "For each cell" twice.

Thanks for your reply.