How can I get an empty cell with a formula
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
"Tom Ogilvy" wrote:
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.
--
Regards,
Tom Ogilvy
"Keith" wrote:
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.
"Tom Ogilvy" wrote:
Use CountBlank instead.
If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And
Application.CountA(Range("D" & i & ":G" & i)) < 4 Then
--
Regards,
Tom Ogilvy
"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?
|