How can I get an empty cell with a formula

[Guest]

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?

 

[Guest]

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

 

[Guest]

Use CountBlank instead.
If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And
Application.CountA(Range("D" & i & ":G" & i)) < 4 Then

 

[Guest]

Thanks for your help but both sugetions above won't work. However I have
found a solution.

Since column E will be filled in automaticaly the code for the Counta will
always be 1 or greater so all I need to do is change the first par from 0 to
1

i.e. instead of

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

I use:

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

My code now works.

Thanks for your help.

 

[Guest]

Since countblank will see the result of "" produced by your formula as a
blank, it is unclear why you say it won't work, but I would assume the
problem lies in implementation rather than in the suggestion.

Glad you figured out your own problem.

 

[Guest]

Hi Tom,

I did try your code. the whole for next loop now looks like this
For i = 3 To 23
If Application.CountBlank(Range("A" & i & ":AF" & i)) > 0 And
Application.CountA(Range("D" & i & ":G" & i)) < 4 Then
flag = True
Exit For
End If
Next i

But this still dosn't work.

As I said I did find a solution, but I didn't want you to think that I
hadn't tried your method.

Thanks Again

Keith

 

[Guest]

If the code you used was the code you showed, then you didn't try out my
suggestion. My suggestion was:

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

to determine if the row was not all blank and then, in that case Dx:Gx was
not filled.

As I said, the problem was in your implementation, not the suggestion.

 

[Guest]

Tom

You are quite right. My sincere apologies. I don't know what the time was
with you yesterday but for me it was getting to the end of a rather stressful
day.

Your solution does work.

Thanks again

Keith