PDA

View Full Version : [SOLVED] Using Ranges with CountBlank



khalid79m
12-01-2008, 07:52 AM
=IF(COUNTBLANK($X3:$AD3),"Data Missing","Complete")

My current code, looks at x3 to ad3 and see if there are any blanks , if yes then "Data Missing" , "Complete"

I need the code to rather than look at x3 to ad3 , look at ranges "Que_1! and "Que_2", "Que_3", "Que_4" and "Que_5"

How can this be done ?


This is my current code:


'Call Checker
With Range("AN3:AN" & lastrow)
.FormulaR1C1 = "=IF(COUNTBLANK($X3:$AD3),"Data Missing","Complete")
.Value = .Value
End With

I want it to


'Call Checker
With Range("Call_Checke")
.FormulaR1C1 = "=IF(COUNTBLANK('!!!put the ranges in here ),"Data Missing","Complete")
.Value = .Value
End With

khalid79m
12-01-2008, 08:15 AM
:banghead:

With Range("Call_Checker")
.FormulaR1C1 = "=IF(COUNTBLANK(Question_8)+COUNTBLANK(Question_9),""Data Missing"",""Call Complete"")"
End With

the only problem is that this looks at the whole range for blanks I only need it to look at the relative row.

for example Call_Checker runs from C3 to C50

and question _ 8 is A3 to A50
and question _ 9 is B3 to B50

when i run the code i need it check a3 and b3 for blanks and put the result on c3

a4 and b4 result in c4 and so on...

Can this be done

Bob Phillips
12-01-2008, 08:51 AM
With Range("Call_Checker")
.Formula = "=IF(COUNTBLANK(INDEX(Question_8,ROW(A1)))+COUNTBLANK(INDEX(Question_9,ROW(A 1))),""Data Missing"",""Call Complete"")"
End With

khalid79m
12-01-2008, 09:27 AM
With Range("Call_Checker")
.Formula = "=IF(COUNTBLANK(INDEX(Question_8,ROW(A1)))+COUNTBLANK(INDEX(Question_9,ROW(A 1))),""Data Missing"",""Call Complete"""
'.FormulaR1C1 = "=IF(COUNTBLANK(Question_8)+COUNTBLANK(Question_9),""Data Missing"",""Call Complete"")"
End With

Hi it comes up with an error message

Run-time error '1004':
Application-Defined or Object-Defined Error

Bob Phillips
12-01-2008, 09:41 AM
Worked fine for me.

Bob Phillips
12-01-2008, 09:42 AM
You seemed to have lost the closing bracket and quotes.

khalid79m
12-01-2008, 09:46 AM
Me being a bit dim, what does the row(a1) ? signify ?

Bob Phillips
12-01-2008, 09:48 AM
That is just used as an index counter, the second item will be ROW(A2), etc.

khalid79m
12-03-2008, 03:59 AM
superstar