# Formula help

#### Donai

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

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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Glory

##### Well-known Member
set therange = Range("")

for each theCell in theRange
if theCell.Value = "" Then
Next

Pretty crude... but maybe something like this?

#### Donai

##### Well-known Member
set therange = Range("")

for each theCell in theRange
if theCell.Value = "" Then
Next

Pretty crude... but maybe something like this?

I am after a formula not vba code

#### jeffreybrown

##### Well-known Member
Where do you want this formula?

#### Donai

##### Well-known Member

Where do you want this formula?

In E1 for now

#### jeffreybrown

##### Well-known Member
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

##### Well-known Member

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

##### Well-known Member
=if(countblank(a1:a10)>0,"error","ok") ?

#### Donai

##### Well-known Member
=if(countblank(a1:a10)>0,"error","ok") ?

Can the ranges be dynamic? From A6:A

##### MrExcel MVP
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...

Replies
4
Views
84
Replies
6
Views
35
Replies
2
Views
68
Replies
9
Views
46
Replies
0
Views
14