Code does not work - Type Mismatch

[twinkle99]

Hi

I have a small table of data as follows:

Book1.xls
	A	B	C	D
1	compdate	targdate	code
2	02/06/200700:00	01/06/200700:00	A
3	18/06/200700:00	02/06/200700:00	D
4	16/08/200700:00	03/08/200700:00	A
5	16/08/200700:00	04/06/200700:00	D
6	15/08/200700:00	05/07/200700:00	D
7	18/08/200700:00	06/06/200700:00	D
8		07/07/200700:00	A
9	12/04/200700:00	08/08/200700:00	B
10	13/04/200700:00	09/05/200700:00	D
11		10/06/200700:00	D
12	22/05/200700:00	11/06/200700:00	D
13	23/05/200700:00	12/05/200700:00	C
14		13/06/200700:00	D
15	12/05/200700:00	14/05/200700:00	E
16	13/05/200700:00	15/06/200700:00	E
17		16/08/200700:00	D
18	16/05/200700:00	17/05/200700:00	A
19	28/05/200700:00	18/06/200700:00	A
Sheet1

I have written a macro which simply counts the number of rows where column A is later than column B and Column C is 'D'.

Dim rng As Range, c As Range
Dim count As Long
count = 0
Set rng = Range("A1:D20")
For Each c In rng
If c.Offset(0, 2) = "D" And c.Offset(0, 0)<> "" And c.Offset(0, 0) > c.Offset(0, 1) Then count = count + 1
Next c
[D1] = count
End Sub

This works as intended - 4 rows.

However, I now want to count rows where Column A is more than 10 days later than Column B. I tried this code but it does not work.

Dim rng As Range, c As Range
Dim count As Long
count = 0
Set rng = Range("A1:E20")
For Each c In rng
If c.Offset(0, 2) = "D" And c.Offset(0, 0)<> "" And c.Offset(0, 0) > c.Offset(0, 1) + 10 Then count = count + 1
Next c
[D1] = count
End Sub

What have I done wrong?

Also, is it possible to exclude weekends when counting the number of days in the macro?, i.e. 10 working days from the 05/07/2007 would be
19/07/2007.

Thanks

 

[brian.wethington]

First off I would strongly urge you to not use count as a variable as it is a keyword in VBA. Not only can this cause programming issues sometimes it can confuse anyone who is trying to make sense of your code.

Sub Test1()

Dim rng As Range, c As Range
Dim x As Long
x = 0
Set rng = Range("A1:A20")
For Each c In rng
If c.Offset(0, 2).Value = "D" And c.Value <> "" And c.Value > c.Offset(0, 1).Value + 10 Then x = x + 1
Next c
Range("D1") = x

End Sub

Also, is it possible to exclude weekends when counting the number of days in the macro?, i.e. 10 working days from the 05/07/2007 would be
19/07/2007.

Why not just write a code that takes 14 days instead of 10?

Lastly I was not sure why you were checking each cell A to E I just left it as A.

 

[twinkle99]

Thanks Brian

I still get Run-Time Error '13' type mismatch on line 6:

If c.Offset(0, 2).Value = "D" And c.Value <> "" And c.Value > c.Offset(0, 1).Value + 10 Then x = x + 1

An ideas?

Thanks

 

[brian.wethington]

First do you really need VBA for this?

=SUMPRODUCT(--(A1:A65535>B1:B65535 + 10),--(C1:C65535="D"))

should do the same thing with a formula as your VBA. However, if Column B of the range you are checking happens to have text in it both will fail (the code will error out and you will receive a #Value!). I guess you have text in column be somewhere?

 

[brian.wethington]

I posted too quick last time.... here is some VBA to take care of the text problem possibly. Some formula guru probably has some tricks to handle it without VBA.

Sub Test1()

Dim rng As Range, c As Range
Dim x As Long
x = 0
Set rng = Range("A1:A20")
For Each c In rng
    If IsDate(c.offset(0, 1).Value) Then
        If c.Offset(0, 2).Value = "D" And c.Value <> "" And c.Value > c.Offset(0, 1).Value + 10 Then x = x + 1
    End If
Next c
Range("D1") = x

End Sub

 

[twinkle99]

Many Thanks Brian

I tried your code and it works perfectly.

Thanks

 

[brian.wethington]

Many Thanks Brian

I tried your code and it works perfectly.

Thanks

Glad I could be of service.