2 VBA Q

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

1. Cells(i, 9) = DateDiff(Cells(i,7), "12/31/2003", "m") -
gives me type mismatch error -
I need to check that the end date is before 12/31/2003 and
then calculate the number of months till the end of the
year.

2. If (Cells(i, 7) = "Complete" & Sheets("Plan").Cells(i,
7) = "mm/dd/yyyy") - does not work.
It works for - If (Cells(i, 7) = "Complete")
However, I need to check - that there is a date in the
cells - Sheets("Plan").cells(i,7)
else I want to leave the original comment - "Complete".

TIA.
 
dipsy,


1) Cells(i, 9) = DateDiff( "m",Cells(i,7), "12/31/2003")
2) If (Cells(i, 7) = "Complete" And Sheets("Plan").Cells(i, 7) =
"mm/dd/yyyy")

HTH
Henry
 
Henry:

Thanks for the response.

I used the first - Cells(i, 9) = DateDiff( "m",Cells
(i,7), "12/31/2003")
and still get the Type Mismatch error.

Also, one more question:

If cells(i,8) contains "Technically Complete" then delete
the row. This cell may have additional info - like:
Technically Complete; Premier Sys Additional Dollars.
even if it has additional info I want to delete the row.
(This is like PERL - catching just one phrase in the cell).

TIA.
 
Dipsy,

Are you sure that Cells(i,7) contains a valid date?
I've just tried it by setting i = 5
I put a date in cell 5,7 and ran it.
I got the correct answer in cell 5,9 every time.
If cells( i,7) contains a date in text format try

Cells(i, 9) = DateDiff( "m", DateValue(Cells(i,7)), "12/31/2003")

For your additional question

If you're looping through all the rows to delete, you'll have to start at
the bottom and go up, so you don't miss any rows.
Look up Find and FindNext in XL help.
Sorry, I know less than nothing about PERL.

HTH
Henry
 
Back
Top