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.
--
Regards,
Tom Ogilvy
"Keith" wrote:
> 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?
> > > > >
|
Similar Threads
