+ Reply to Thread
Results 1 to 9 of 9

IfThenElseIf in VBA problem

  1. #1
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    IfThenElseIf in VBA problem

    What I'm trying to accomplish is to have the following code search from cell Q4 down to the last cell that is not null. The previous part of the macro ran an autofill of a formula that inserted either OK or ERROR in the cell. What I would like this function to perform is to search the range and if any cell has the word ERROR in it, a message box appears to inform me there is an error that needs to be fixed and the macro ends. The ElseIf returns a message box that No errors were found and to click OK to continue with the macro.

    I've been searching and trying to piece this together myself, but it is now time to seek out help for more experienced people.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: IfThenElseIf in VBA problem

    Please Login or Register  to view this content.
    Last edited by snb; 01-04-2012 at 12:37 PM.



  3. #3
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: IfThenElseIf in VBA problem

    Thanks SNB. I'm not sure how this code works, but it does. It looks like I was way off with my code.

    After thinking about this, I went back and changed some of the cells to read "ERROR" and the code you provided does not work. Even with the error in the cells, the message box pops up with "No errors found. Click OK to continue." which isn't what it should do. There is a merge cell E1:F1 and so I changed the code to reference column 16 just in case that might have caused a problem, but it still doesn't work.

    Can anyone else offer another solution for this?
    Last edited by BBoydAnchor; 01-04-2012 at 02:30 PM.

  4. #4
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: IfThenElseIf in VBA problem

    Here is some additional code from the macro in case this will be useful.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: IfThenElseIf in VBA problem

    Mayhap

    Please Login or Register  to view this content.
    will achieve what you need?
    Good luck.

  6. #6
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: IfThenElseIf in VBA problem

    Thanks OnErrorGoTo0. Your code I can understand better, but when I run the macro, I get the following error.

    Microsoft Visual Basic
    Run-time error '1004':
    Method 'Range' of object '_Global' failed

    What would cause this and how do I correct it?

  7. #7
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: IfThenElseIf in VBA problem

    I confess I had assumed that LastRow was either a function or a module-level (or public) variable. It seems not so you are trying to apply row 0 to a range. You need to move the declaration of LastRow out of your other procedure and above all routines in the module.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: IfThenElseIf in VBA problem

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-15-2011
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: IfThenElseIf in VBA problem

    Quote Originally Posted by OnErrorGoto0 View Post
    I confess I had assumed that LastRow was either a function or a module-level (or public) variable. It seems not so you are trying to apply row 0 to a range. You need to move the declaration of LastRow out of your other procedure and above all routines in the module.
    I modified your code with something that was in the code snb shared and it works. Thank you for the help.

    Please Login or Register  to view this content.

+ 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