help is needed

[MERKY]

i have cells where the user will input data and then this data will be transferred to another sheet. I would like to do a presence check to make sure that data has been typed into all the cells. The cells i would like to do this check on are
C3,C5,C7,C9 and C11. Is there any way to do this?

[Jack in the UK]

hi

Sorry your [rofile does not state loaction, but i keep reading ****presence check****

sorry i do not understand this comments.. need street translation if that ok

is this new Americal expression???? im lost

i guess you mean .. check if new entered data in column has never been entered EXACTLY as is before..

CORRECT?

[MERKY]

I NEED TO CHECK TO MAKE SURE THAT THE USER HAS NOT LEFT CELLS BLANK BY ACCIDENT. ALL THE CELLS SHOULD HAVE DATA IN THEM.

[Tom Schreiner]

What kind of data, Merky?
Dates,Text,Numbers,ect...
One or two or all?
Also, how are you transfering your data?
Macro, I guess?
An event?
Tom

[ This Message was edited by: TsTom on 2002-04-30 14:46 ]

[MERKY]

the data will be text and it will be transferred using a macro. so far i have this formula(below). but the problem with this is that it only allows me to check cells which are in a list. I want to be able to check cells which are not in a list. For example would like to check cells C5,C7,C9,C20 and C21.
HOW CAN I USE THE FORMULA BELOW BUT TO CHECK THE CELLS I HAVE LISTED ABOVE?


Sub aaa()
Range("B1:B3").Select
ran1 = Range("B1:B3")
rowcount = WorksheetFunction.CountA(ran1)
mycount = Selection.Count
If rowcount <> mycount Then
MsgBox ("make sure that u have entered all the data needed")
End If
End Sub

[Jay Petrulis]

You can try something like...
'------------------
Sub test()
Dim x As Integer
x = WorksheetFunction.CountA(Range("C3,C5,C7,C9,C11"))

If x = 5 Then
MsgBox "Transfer your cells"
Else
MsgBox "Not all cells filled in. Please retry."
Exit Sub
End If

'rest of your code

End Sub
'-----------------

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-04-30 15:05 ]

[MERKY]

the formula Jay is good but it doesnt work 100%. he problem is that it only tells the user that they need to fill the cells in if all the cells are blank, but if for example 1 cell is left blank the formula thinks that the user ahas filled in all the cells.

what i need is a formula that will tell arn the user even if they have left one cell empty.

thankz

[Tom Schreiner]

Try...

Sub Validate()

Dim c

For Each c In Range("C3,C5,C7,C9,C11")

    If c = "" Then

        MsgBox "Please enter data in cell " & c.Address

        c.Select

        Exit Sub

    End If

Next

End Sub

[ This Message was edited by: TsTom on 2002-04-30 15:25 ]

[Aladin Akyurek]

Merky,

Select the target cells one by one while you hold the control key pressed. Then go to the Name Box on the Formula Bar, type Irange, and hit enter.

Enter in some cell:

=COUNTA(Irange)=5

This formula will give FALSE if any of the cells is left empty by the user; if all filled, you get TRUE.

The macro should look at this cell before transferring the data from these cells to a target sheet. That's something I leave to macro coders.

Hope this is what you want.

One thing though: There is no need for all in capitals. Many people "read" that as shouting. & it's hard to read.

Cheers.

Aladin

[MERKY]

Thankz to all the people who helped me on this problem. Dam, u guys must be proper smart. Anyhow , thankz.