If I understand you correctly then this logic should work:

If Application.CountA(Range("A" & i & ":AF" & i)) 0 And
(Application.CountA(Range("D" & i & ":G" & i)) < 4 Or Range("E" & i).Value =
"") Then

Cheers,
Dave

"Keith" wrote:

I have a routine that runs when I save the workbook. It looks along rows 3 -
23 and columns A - AF. If there is an entry in any of the cells in a row
then the cells for that row in columns D - G must have a value.

The mail part of the code looks like this:

If Application.CountA(Range("A" & i & ":AF" & i)) 0 And
Application.CountA(Range("D" & i & ":G" & i)) < 4 Then

where i is the value in a for next loop going from 3 - 23.

This routine works great. Unfortunatly I now need column E to be
automaticaly entered based on a Vlookup and the value in column D. The
formula for E is:

=IF(D3<"",VLOOKUP(D3,HospitalCity,2),"")

This works as well but for some reason column E now has a count of 1.

Is there any way that I can have the Counta of column E equal 0 if there is
no entry in it?I have a routine that runs when I save the workbook. It looks
along rows 3 - 23 and columns A - AF. If there is an entry in any of the
cells in a row then the cells for that row in columns D - G must have a value.

The mail part of the code looks like this:

If Application.CountA(Range("A" & i & ":AF" & i)) 0 And
Application.CountA(Range("D" & i & ":G" & i)) < 4 Then

where i is the value in a for next loop going from 3 - 23.

This routine works great. Unfortunately I now need column E to be
automatically entered based on a Vlookup and the value in column D. The
formula for E is:

=IF(D3<"",VLOOKUP(D3,HospitalCity,2),"")

This works as well but for some reason column E now has a count of 1.

Is there any way that I can have the Counta of column E equal 0 if there is
no entry in it?