Posts by rabsofty

    another way to do it.


    if your option buttons are in a frame, another quick way is to set and unset a button
    (option buttons act as a group of option buttons in a frame - when one is true, the other(s) go false)
    eg:
    optionbutton1=true
    optionbutton1=false

    here you go


    Code
    Dim LastRow As Long
    Dim lastCol As Long
    LastRow = Range("a1").End(xlDown).Row
    lastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    [COLOR=#0000FF]Range(Cells(LastRow + 1, 3), Cells(LastRow + 1, lastCol)).Formula = "=SUM(c2:c" & LastRow & ")"[/COLOR]
    'need code to autofill sum formula to lastcol

    thanks, I see!
    you and'ed <> C and also had it in counifs stmt


    so countifs counts all entries that equal b1 but not if any are C in a1:a9 and the And a1<>"C" checks if the row its on is also not C


    I was simply looking for a better way than looping through each entry Or getting count of I's and E's to only work on
    to find a duplicate entry in col B if col A did not Contain a C


    once I put the formula in the column, I can easily do a count in that column for any entries that are true (duplicated)


    That's what I get for working on my program for 6 hours yesterday.


    Thanks a whole bunch

    I have the following table
    Col A Col B Col C [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 192"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]I[/TD]
    [TD="width: 64, bgcolor: transparent"]Dog[/TD]
    [TD="width: 64, bgcolor: transparent"] [/TD]
    [TD="width: 64, bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]I[/TD]
    [TD="bgcolor: transparent"]Cat[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]I[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]E[/TD]
    [TD="bgcolor: transparent"]Nose[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]E[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Rats[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Pigs[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Cows[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]TRUE[/TD]

    [/tr]


    [/TABLE]

    in column C I have the formula


    Code
    =COUNTIFS($B$1:$B$9,B1,$A$1:$A$9,"<>C*")>1


    I want to check if column B is duplicated, but only if Col A <> "C"


    in the table above, mouse appears more than once.
    in the countifs formula I did not want entries that have a C in Column A


    True should only show up for I and E entries that are a duplicate and do not have a C in column A.


    I have looked at this for many hours and am stumped.
    if I read the countifs formula description correctly, the above formula counts B1 in b1:b9 and a1:a9 counts <>C*


    What am I missing


    the results should be:
    Col A Col B Col C [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 192"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]I[/TD]
    [TD="width: 64, bgcolor: transparent"]Dog[/TD]
    [TD="width: 64, bgcolor: transparent"] [/TD]
    [TD="width: 64, bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]I[/TD]
    [TD="bgcolor: transparent"]Cat[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]I[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]E[/TD]
    [TD="bgcolor: transparent"]Nose[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]E[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Rats[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Pigs[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Cows[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]C[/TD]
    [TD="bgcolor: transparent"]Mouse[/TD]
    [TD="bgcolor: transparent"] [/TD]
    [TD="bgcolor: transparent, align: center"]FALSE[/TD]

    [/tr]


    [/TABLE]

    I have attached a spreadsheet.