Solved: Countblank Named Ranges

[khalid79m]

[VBA].Formula = "=IF(COUNTBLANK(INDEX(Question_1,ROW(A1)))+COUNTBLANK(INDEX(Question_2,ROW( A1)))+COUNTBLANK _
(INDEX(Question_3,ROW(A1)))+COUNTBLANK(INDEX(Question_4,ROW(A1)))+COUNTBLAN K(INDEX(Question_5,ROW(A1))) _
+COUNTBLANK(INDEX(Question_6,ROW(A1)))+COUNTBLANK(INDEX(Question_7,ROW(A1)) )+COUNTBLANK(INDEX(Question_8, _
ROW(A1)))+COUNTBLANK(INDEX(Question_9,ROW(A1)))+COUNTBLANK(INDEX(Question_1 0,ROW(A1)))+COUNTBLANK _
(INDEX(Question_11,ROW(A1)))+COUNTBLANK(INDEX(Question_12,ROW(A1)))+COUNTBL ANK(INDEX(Question_13,ROW(A1))) _
+COUNTBLANK(INDEX(Question_14,ROW(A1)))+COUNTBLANK(INDEX(Question_15,ROW(A1 ))),""O"",""P"")"[/VBA]

this simply counts blanks in the named range and dispalys o or p. I need the formula to count only to question 7 if the range named "CHECK" is equal to 1 and if its then count question 1,2,3,5,6,7 (missing question 4)

in a nut shell if range CHECK is equal to 1 then Countblanks for Questions 1 to 7 , but if it is 2 the Countblanks for question 1 to 3 and 5 to 6.

Can anyone help. Spent a whole day on this

[macropod]

Hi khalid79m,

Presumably you could use something based on:

Sub Test()
Dim MyFormula As String
MyFormula = "=IF((CHECK=1)*("
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_1,ROW(A1)))+COUNTBLANK(INDEX(Question_2,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_3,ROW(A1)))+COUNTBLANK(INDEX(Question_5,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_6,ROW(A1))))+COUNTBLANK(INDEX(Question_4,ROW(A1)))+"
MyFormula = MyFormula & "(CHECK=2)*COUNTBLANK(INDEX(Question_7,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_8,ROW(A1)))+COUNTBLANK(INDEX(Question_9,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_10,ROW(A1)))+COUNTBLANK(INDEX(Question_11,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_12,ROW(A1)))+COUNTBLANK(INDEX(Question_13,ROW(A1)))+"
MyFormula = MyFormula & "COUNTBLANK(INDEX(Question_14,ROW(A1)))+COUNTBLANK(INDEX(Question_15,ROW(A1)))"
MyFormula = MyFormula & ",""O"",""P"")"
ActiveCell.Formula = MyFormula
End Sub

Your specifications for the CHECK tests are confusing (for '1', "count question 1,2,3,5,6,7" and for '2' "question 1 to 3 and 5 to 6") - except for Question_7 they amount to the same thing - and it is not at all clear where Question_4 and Question_8 thru Question_15 fit in. Note the re-positioning of the Question_4 test so that it is excluded from both tests (as are Question_8 thru Question_15).

[khalid79m]

7Sorry maybe i didnt explain it properly,

if the range Check =1, then count blanks for question 1 ,2 ,3 ,4 ,5 ,6 ,7
if the range Check =1, then count blanks for question 1 ,2 ,3 ,5 ,6 ,7

[macropod]

Hi khalid79m,

OK, but what about Question_8 thru Question_15? Also, can the CHECK value be anything other than 1 or 2 and, if so, what happens then?

[khalid79m]

I dont need question 8 to 15 anymore. The check option can only be 1 or 2

hence if

if the range Check =1, then count blanks for question 1 ,2 ,3 ,4 ,5 ,6 ,7
if the range Check =1, then count blanks for question 1 ,2 ,3 ,5 ,6 ,7