Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: help is needed

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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


  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •