Formula help

[Donai]

Hi, i need a formula that will look for blanks in Col A6:A, if blank found then return error else ok

Excel Workbook
	A	B
3	JP Morgan	Test
4	JP Morgan	Test
5	JP Morgan	Test
6	JP Morgan	Test
7	JP Morgan	Test
8	JP Morgan	Test
9	JP Morgan	Test
10	JP Morgan	Test
11	JP Morgan	Test
12	JP Morgan	Test
13	JP Morgan	Test
14	JP Morgan	Test
15	JP Morgan	Test
16		Test
17	JP Morgan	Test
18	JP Morgan	Test
19	JP Morgan	Test
20	JP Morgan	Test
21	JP Morgan	Test
22	JP Morgan	Test
23	JP Morgan	Test
24		Test
Sheet2

 

[Glory]

set therange = Range("")

for each theCell in theRange
if theCell.Value = "" Then
msgBox "Error" & theCell.address
Next

Pretty crude... but maybe something like this?

 

[Donai]

set therange = Range("")

for each theCell in theRange
if theCell.Value = "" Then
msgBox "Error" & theCell.address
Next

Pretty crude... but maybe something like this?

I am after a formula not vba code

 

[jeffreybrown]

Where do you want this formula?

 

[Donai]

Where do you want this formula?

In E1 for now

 

[jeffreybrown]

In E1 for now

By this comment, are you looking for just one formula in E1 that evaluates everything in one go?

Based on your sample data, what would be the result in E1?

 

[Donai]

By this comment, are you looking for just one formula in E1 that evaluates everything in one go?

Based on your sample data, what would be the result in E1?

Yes i want one formula that evaluates everything, if blanks found then return error else Ok

 

[energman58]

=if(countblank(a1:a10)>0,"error","ok") ?

 

[Donai]

=if(countblank(a1:a10)>0,"error","ok") ?

Can the ranges be dynamic? From A6:A

 

[Aladin Akyurek]

Can the ranges be dynamic? From A6:A

=IF(COUNTBLANK(A6:INDEX(A:A,MATCH(REPT("z",255),A:A))),"Error","Ok")

The range is assumed to house text values...