VBA code for an error

[Xrull]

Good Day to you all,
I'm trying to write a VBA code to place the value of these formulas in the cells.

=IF(ISERROR(IF(D3="","",I3-F3)),"Re-check",(IF(D3="","",I3-F3)))

or

=IF(ISERROR(IF(D3<>"",I3-F3,"")),"Re-check",(IF(D3<>"",I3-F3,"")))

These formulas are subtracting dates.
I need it to run the length of Column N.
I tried to write a code, I'm unable to overcome the error a text is placed in the a cell in column L or column I in the but I'm afraid it is not making any sense to me.
Please help,
Xrull

 

[Legacy 68668]

Your formula does not make sense to me...

What does IsError function evaluate in your formula?

IsError("")
IsError(Number)

????

IsError function evaluates if the argument is Error or not.

 

[Xrull]

Seiya,
Sometimes I data that is not a date format (when I'm tired or not being careful), and I need to see where there is an error to go back and put the correct date in there.
When I tried writing a code, similar to the one you gave me earlier today, it just doesn't work.

Sub Macro15() 'Invoice date to post date
 With Sheets("Entry")
      With .Range("M3:M" & .Range("D" & Rows.Count).End(xlUp).Row)
          .Formula = "=IF(L3" <> ""","""",L3 - I3)"
         .Value = .Value
      End With
  End With
End Sub

It doesn't subtract.
Thanks,
Xrull

 

[Legacy 68668]

Seiya,
Sometimes I data that is not a date format (when I'm tired or not being careful), and I need to see where there is an error to go back and put the correct date in there.
When I tried writing a code, similar to the one you gave me earlier today, it just doesn't work.

Sub Macro15() 'Invoice date to post date
 With Sheets("Entry")
      With .Range("M3:M" & .Range("D" & Rows.Count).End(xlUp).Row)
          .Formula = "=IF(L3" <> ""","""",L3 - I3)"
         .Value = .Value
      End With
  End With
End Sub

It doesn't subtract.
Thanks,
Xrull

You are not explaining your porblem.

What is the correct date and where do you mean there ?

 

[Xrull]

Seiya,

I am subtracting 2 dates, the dates are in columns L and I.

The code I wrote returns "true" in the column a cells instead of giving me the amount of days between the dates.

I want a code than can subtract the dates, or return an error message in the cells of column M if I put date that is not in the correct format (1/1//2009) or any other entry that is not a date entry in a cell in either column L or column I.

Thanks again,
Xrul

 

[jasonb75]

I agree with Seiya about not making sense, but came up with this from a bit of guesswork.

Assumes dates are in columns I and L, earlier date in I
Assumes that this formula is going in column M
Assumes that if column D is blank M is to be left blank
Assumes that you want the result in the cell, not the formula.

Sub Macro15() 'Invoice date to post date
 With Sheets("Entry")
      For a = 3 To Range("D" & Rows.Count).End(xlUp).Row
      Range("M" & a) = "=IF(D" & a & "="""","""",IFERROR(L" & a & "-I" & a & ",""Re-check""))"
      Range("M" & a) = Range("M" & a).Value
      Next
      End With
 
End Sub

 

[Xrull]

jasonb,
The formula you gave me is returning "#Name?"

Column D holds the names of persons, Column I is for the entry date, and Column L is for the end date. I need to find the amount of days between the start and and end date in cells in Column M. In the event I put something that is not a date in Column I or L I'd like the cell in the row with the error to return the message Re-Check.

Ignore my previous codes.

Try to replicate this formula (or a more efficient formula) using VBA:

=IF(ISERROR(IF(D3="","",I3-F3)),"Re-check",(IF(D3="","",I3-F3)))

I don't know, but maybe the VBA code doesn't need the If iserror statement.

Thanks for trying to make some sense out of my nonsense,
Xrull

 

[jasonb75]

ok, it's making more sense now,
that code was working for me, are your dates in a recognised excel date format?

To find out, save your workbook, then select the whole column with the dates and change the cell format to number, if it changes to a number like 39885.00 then it's valid, if it stays as a date then it's not.

 

[Xrull]

Jasonb,
I followed your instructions, and the dates changed to numbers (with the exception of the cell I want to show an error), and ran the formula again, but the cells returned #Name?.
I tried adjusting your code:

Sub Macro15() 'Invoice date to post date
 With Sheets("ENTRY")
      For a = 3 To Range("D" & Rows.Count).End(xlUp).Row
      Range("M" & a) = "=IF(D" & a & "="""","""",IFERROR(I" & a & "-L" & a & ",""Re-check"",I" & a & "-L" & a & ")))"
      Range("M" & a) = Range("M" & a).Value
      Next
      End With
 
End Sub

But my attempts are making people on this site cringe (it didn't work). If Excel police existed, I'd be doing life for mangling codes like this.
I thinking Excel doesn't like me
Thanks again,
Xrull

 

[jasonb75]

Ok, made a couple of minor changes but nothing drastic, still can't see why it didn't work before

Data in column M entered by the macro.

Excel Workbook
	D	I	L	M
3	name	13/03/2008	13/03/2009	365
4	name	13/03/2008	13/03/2009	365
5	name	13/03/2008	13/03/2009	365
6	name	13/03/2008	13/03/2009	365
7	name	13/03/2008	13/03/2009	365
8	name	13/03/2008	13/03/2009	365
9	name	nodate	13/03/2009	Re-check
10	name	13/03/2008	13/03/2009	365
11	name	13/03/2008	13/03/2009	365
12	name	13/03/2008	13/03/2009	365
13	name	nodate	13/03/2009	Re-check
14		13/03/2008	13/03/2009
15		nodate	13/03/2009
16	name	13/03/2008	13/03/2009	365
17	name	13/03/2008	13/03/2009	365
18	name	13/03/2008	13/03/2009	365
19	name	13/03/2008	13/03/2009	365
20	name	13/03/2010	13/03/2009	Re-check
entry

Sub Macro15() 'Invoice date to post date
 With Sheets("Entry")
      For a = 3 To Range("D" & Rows.Count).End(xlUp).Row
      Range("M" & a) = "=IF(D" & a & "="""","""",IFERROR(L" & a & "-I" & a & ",""Re-check""))"
      If Range("M" & a).Value < 0 Then Range("M" & a) = "Re-check"
      Range("M" & a) = Range("M" & a).Value
      Next
      End With
 
End Sub