help is needed

MERKY

New Member
Joined
Apr 29, 2002
Messages
38
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try...<pre>

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</pre>
This message was edited by TsTom on 2002-04-30 15:25
 
Upvote 0
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
 
Upvote 0
Thankz to all the people who helped me on this problem. Dam, u guys must be proper smart. Anyhow , thankz.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top