type mismatch in do loop

[spinkung]

Hi All,

I'm trying to loop through an excel range of dates from access vba. I want the loop to end when the date in my range matches the the current year and month. Problem is that not every cell is in date format so i could have...

01/01/09, 01/02/09, total 09, etc....

when it get to the total 09 cell the loop fails and says type mismatch. Is there a way to handle this??

Do
  
Loop Until Month(xlsht.cells(iRow, iColCount).Value) = Month(Now()) And _
                                Year(xlsht.cells(iRow, iColCount).Value) = Year(Now())

many thanks, spin.

 

[namliam]

try something like so:

Do
  if left(yoursell,5) = 'Total' then exit loop
  if Month(xlsht.cells(iRow, iColCount).Value) = Month(Now()) And _
                                Year(xlsht.cells(iRow, iColCount).Value) = Year(Now()) then exit loop
Loop

If you just want to continue the loop, use an if .. .then else structure like so:

Do
  if left(yoursell,5) = 'Total' then 
  else
    if Month(xlsht.cells(iRow, iColCount).Value) = Month(Now()) And _
                                  Year(xlsht.cells(iRow, iColCount).Value) = Year(Now()) then exit loop
  end if 
Loop

 

[spinkung]

Thanks for your reply. It has worked a treat.

Do
If xlsht.cells(iRow, iColCount).Value Like "To*" Then
          iColCount = iColCount + 1
End If

Loop Until Month(xlsht.cells(iRow, iColCount).Value) + 1 = Month(Now()) + 1 And _
Year(xlsht.cells(iRow, iColCount).Value) = Year(Now())

spin