+ Reply to Thread
Results 1 to 10 of 10

If condition inside a for loop

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Boise, Idaho, USA
    MS-Off Ver
    2011
    Posts
    11

    If condition inside a for loop

    Hi guys,

    I am encountering an issue again with the program I have been developing for the last couple month and hopefully i will find good help.

    I have a For loop that looks Inside a range of cells, depending on the value it gives me a data kept Inside the database. This being said, I have cells that are empty and so it displays an error.

    I tried to put in the loop an IF function to check the empty condition but it does not work. I also tried to Go To Next R on Error but failed again.

    This is on the Macro "Dailyreport" right before line 505.

    The main goal is to ignore the empty cells and move to the next line. It looks pretty simple on paper but I struggled for 2 days on that thing.

    Any help would be awesome.
    Thanks
    Ronanvba error.pngMacrosRD - V3.xlsm

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,804

    Re: If condition inside a for loop


    Hi !

    Only one On Error Goto codeline is necessary !

    But by checking array variable Data size via UBound function, you don't need any !

    Are you sure of G column ? In Sheet1 that's Quantity ! …

  3. #3
    Registered User
    Join Date
    12-10-2014
    Location
    Boise, Idaho, USA
    MS-Off Ver
    2011
    Posts
    11

    Re: If condition inside a for loop

    Bonsoir Marc et merci!

    G5 down to the end is used to count how many rows there are. The variable is taken from Rng.beg E1.

    I know that only one Go to Error is necessary but tonight I was so desperate that I tried that ... and duh it did not work

    Thank you for your help.

    Ronan

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,804

    Exclamation Re: If condition inside a for loop


    Mama mia ‼

    In your piece of code, RngBeg variable is G5 cell.
    So With Wks.Cells(r, RngBeg.Column) codeline is relative to Worksheet "Sheet1" column #7 aka G so Quantity !
    'Cause this column is numeric, can't have two minus signs inside So are you sure of G column ?!

  5. #5
    Registered User
    Join Date
    12-10-2014
    Location
    Boise, Idaho, USA
    MS-Off Ver
    2011
    Posts
    11

    Re: If condition inside a for loop

    I understand your point. Though, I have been using this code for the last 4 month and it worked perfectly. The only difference is that the column that is being looked at has now empty cells due to a change in my software.
    Tomorrow i will attach to this thread a data file that I run the macro on. It will make more sense.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,804

    Re: If condition inside a for loop



    In fact your code seems to work but just 'cause of On Error Goto codeline ! A waste of process time …

  7. #7
    Registered User
    Join Date
    12-10-2014
    Location
    Boise, Idaho, USA
    MS-Off Ver
    2011
    Posts
    11

    Re: If condition inside a for loop

    Alright, attached is a data file that I use on a daily basis.
    When I run it I use the MacroV3 file that contains my database then I open the file with all the gross data.

    When running the macro Dailyreport we have an error due to the column of GL Accounts that is without value. w29.xlsx

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,804

    Re: If condition inside a for loop


    'Cause of code bad conception, you must explain the purpose of the Dailyreport procedure !
    For example, ActiveSheet means which worksheet of which workbook ?

    Error ? Which one ? Message ? In which codeline ?

    Read post #2, you already have a way to avoid it …

  9. #9
    Registered User
    Join Date
    12-10-2014
    Location
    Boise, Idaho, USA
    MS-Off Ver
    2011
    Posts
    11

    Re: If condition inside a for loop

    Marc,

    The code is not perfect and its conception has been evoluting with the help of numerous people here, though I am not sure we can talk about bad conception.

    I was able to place the relevant line code On error Resume Next and it works now.

    Thank you for your help anyway.

    Ronan

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,804

    Arrow Re: If condition inside a for loop


    A well code conception does not need multiple On Error Goto statement like in yours
    And so many ways to avoid it !

    This statement just hides problems, not resolves them

    And one day you should ask on a forum like many others :
    I don't understand, my code does not work but there is no error message !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to restrict pop of message box inside multiple times when it is inside a for loop?
    By timmu in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2014, 06:00 AM
  2. [SOLVED] For loop inside for loop starts over.
    By lavinius in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2013, 08:57 AM
  3. [SOLVED] For Next Loop with IF statement inside does not loop
    By roxybethany in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-09-2013, 11:45 AM
  4. Changing the index of a for loop inside of the loop
    By drinkmorewine in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-03-2013, 12:19 PM
  5. if condition inside msgbox
    By kash in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2012, 08:24 AM
  6. [SOLVED] Loop inside a loop problem!
    By questionguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2012, 12:54 PM
  7. VBA For loop where number of iterations can be changed from inside loop
    By barrboy89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-11-2011, 12:18 PM

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