I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".

If yes and no are in Col A
then put this in B1
=IF(COUNTA(A:A)=COUNTIF(A:A,"yes"),"Yes","No")

if you know the last cell in Col A (say A100) then you can replace A:A by
A1:A100 in the formula and put the formula in A101

"NebJoel" wrote:

I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".

Try the following:

Sub makechange()
Set r = Range("A1:A100")
If Application.WorksheetFunction.CountIf(r, "Yes") = 100 Then
Exit Sub
Range("A100").Value = "No"
End Sub

If the column is A1 thru A100 and all the values are "Yes", then A100 is
already "Yes" and no change is required. If any value is not "Yes" then A100
is set to "No"

Adjust the range to suit your needs.
--
Gary''s Student - gsnu200832


"NebJoel" wrote:

I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".

=IF(COUNTIF(A2:A10,"Yes")=COUNTA(A1:A10),"Yes","No ")


HTH,
Bernie
MS Excel MVP


"NebJoel" wrote in message
...
I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".

This assumes NO header row. If a header row change to =lr-1

Sub ifallyes()
mc = "i"
lr = Cells(Rows.Count, mc).End(xlUp).Row
If Application.CountIf(Columns(mc), "yes") = _
lr Then Cells(lr, mc) = "No"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NebJoel" wrote in message
...
I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column
is
all "Yes" then change the last cell to "Yes" else change last cell to
"no".

How would u like this one?

=if(sum((A1:A11="yes")^2)-row(A11)+row(A1);"yes";"no")

press ctrl+shift+enter after u insert in calculation cell

if anything else than "yes" is in A1:A11, my formula returns "no"

"NebJoel" wrote:

I have an issue that I have not seen posted. I need Excel to read a column
of cells with one of two expressions inside (yes or now). If the column is
all "Yes" then change the last cell to "Yes" else change last cell to "no".