+ Reply to Thread
Results 1 to 5 of 5

Checking a value in a cell above the selected cell

  1. #1
    Registered User
    Join Date
    01-17-2011
    Location
    calgary, alberta
    MS-Off Ver
    Excel 2003
    Posts
    61

    Checking a value in a cell above the selected cell

    Does anyone know the code for checking a value in a cell above the cell that is currently selected? I will try to explain.

    I have a range (B17:B43) and when the user selects a cell in that range, the code will look in the cell directly above the one that is selected, and check if it contains the word "logging". If it does then it checks to see if a cell in the same row but a different column is populated. If it isn't, then a msgbox appears and will populate that cell with the users inputs.

    I will try to explain further with my comments. this is what i have so far.

    Please Login or Register  to view this content.
    Last edited by calmena; 03-23-2011 at 10:29 AM.

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Checking a value in a cell above the selected cell

    Hi,

    I haven't checked the logic of your code and you didn't mention what problem(s) you are having with it. However, I did immediately notice that you have at least one (unfortunate) typo:
    Please Login or Register  to view this content.
    If you declare you variables and enforce declaration with Option Explicit, then typos such as this one will be picked up for you.
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    01-17-2011
    Location
    calgary, alberta
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Checking a value in a cell above the selected cell

    Thanks for catching that, however I have no idea how to declare variables with option explicit as you mentioned.

    The problem that I am having is that the two lines of code that are commented "this isn't working" are not working. what is happeing is that when I comment out those two lines of code, (PrevRow =, and PrevTarget =), the rest of the code works well, but its checking the current target cell for the text "logging", not the cell above the currently selected cell. So I need to point it to look at the cell above the selected target cell. This is kind of hard to explain, I hope that helps.

  4. #4
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Checking a value in a cell above the selected cell

    hi, calmena,

    try this out:

    Please Login or Register  to view this content.
    Colin gave you very useful advice on Option explicit usage. It requires all the variable to be declared as in example above.

    VB help for Option Explicit:

    Syntax

    Option Explicit

    Remarks

    If used, the Option Explicit statement must appear in a module before any procedures.

    When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

    If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.

    Note Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear.

  5. #5
    Registered User
    Join Date
    01-17-2011
    Location
    calgary, alberta
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Checking a value in a cell above the selected cell

    Thank you so much! its works perfect!

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1