+ Reply to Thread
Results 1 to 17 of 17

Blank cell must not stop the macro

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Blank cell must not stop the macro

    Hi. I came over that piece of code that help me convert numbers (>1) in one row into multiple row with one number (1). The macro is made to stop over a blank cell. This my problem I can't resolve. Right now, I'm doing it manually on every group of numbers until it reach a blank cell. It is sooo long! I try some modifications but without success. I'm sure it's easy but I can't figure it out. Also, once figure it out, I could write something a the end of my column to stop the macro (like "N/A" or whatever).

    I'm sure someone has a clue and help would be greatly appreciated.

    Thanks!

    Here's the Macro:

    Sub Multicator_Line_Page()

    ' Line Multicator
    ' Divide every group (row) into units
    ' Author: no me (a friend).
    ' Hotkey: Ctrl+p

    Dim IQte, itmp As Integer
    Dim sRow, sColumn As String
    Dim erreur, sortie As Integer
    erreur = 0
    sortie = 0

    Do Until (IsNumeric(ActiveCell) = False) Or (erreur = 1) Or (sortie = 1)

    If IsNumeric(ActiveCell) And ActiveCell.Value > 0 Then
    IQte = ActiveCell.Value
    itmp = IQte
    ActiveCell.Value = 1
    sRow = ActiveCell.Row
    sColumn = ActiveCell.Column

    Rows(ActiveCell.Row).Select
    Selection.Copy
    Do Until itmp < 2
    Selection.Insert Shift:=xlDown
    Rows(ActiveCell.Row).Select
    Selection.Copy
    itmp = itmp - 1
    Loop

    ' next entry
    ActiveCell.Offset(IQte, sColumn - 1).Select

    'empty cell
    If IsEmpty(ActiveCell.Value) Then
    sortie = 1
    MsgBox "That's it!"
    End If

    Else
    MsgBox "Error, this is not a number or number <= 0!"
    erreur = 1

    End If
    Loop

    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Blank cell must not stop the macro

    try this
    HTML Code: 
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Lookin' good!

    There seems to be a problem with this line:

    Lastrow =Cells(Rows.Count, sColumn).End(xlUp).Row]


    My worksheet look like this (in different columns):

    NW 3
    NE 2
    SE ""
    SW 3
    NW 2

    I would like to have that:

    NW 1
    NW 1
    NW 1
    NE 1
    NE 1
    SE ""
    SW 1
    SW 1
    SW 1
    NW 1
    NW 1

    And so on...


    Thanks!

  4. #4
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Does anyone else has a clue?
    It must be easy. Only the blank cells must be jumped over.

    Should'nt Lastrow be dim_ed?

  5. #5
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    Quote Originally Posted by madmot View Post
    There seems to be a problem with this line:

    Lastrow =Cells(Rows.Count, sColumn).End(xlUp).Row]
    Of course there is a problem, remove the ] at the end.

    If you have option explicit on, then just add a line of
    Please Login or Register  to view this content.
    anywhere above it.



    I didn't look through the entire code to figure out what it is doing though.

  6. #6
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Damn! This same line keeps giving me runtime error 1004: Application-defined or objet-defined error...

    Lastrow = Cells(Rows.Count, sColumn).End(xlUp).Row

    My last row as a unique value of 107 000. Can't we tell the macro to continue until Activecell.Value = 107 000 no matter if it's a numeric value or blank ("")?

  7. #7
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    I just read through a little, and editing from patel45's post.

    Try see if this helps
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    I must say, i really appreciated youy help.

    It really does'nt like the Lastrow thing. It hangs on it hard.

    I almost had succes with something like this but I'm not so sure of what i'm doing.

    Dim IQte, itmp As Integer
    Dim sRow, sColumn As String
    Dim erreur
    erreur = 0
    sColumn = ActiveCell.Column
    Do Until (ActiveCell.Value = 107000)

    If IsEmpty(ActiveCell.Value) Then
    ActiveCell.Offset(IQte, sColumn - 1).Select
    ' sortie = 1
    ' MsgBox "That's it!"
    End If

    If IsNumeric(ActiveCell) And ActiveCell.Value > 0 Then
    IQte = ActiveCell.Value
    itmp = IQte
    ActiveCell.Value = 1
    sRow = ActiveCell.Row
    sColumn = ActiveCell.Column

    Rows(ActiveCell.Row).Select
    Selection.Copy
    Do Until itmp < 2
    Selection.Insert Shift:=xlDown
    Rows(ActiveCell.Row).Select
    Selection.Copy
    itmp = itmp - 1
    Loop

    ' next entry
    ActiveCell.Offset(IQte, sColumn - 1).Select

    'empty cell

    Else
    'MsgBox "Error, this is not a number or number <= 0!"
    'erreur = 1

    End If
    Loop

    I want it to check first if the cell is blank. If so then column -1 (next down)
    Then if there are numbers, the macro does what it needs to do. The problem is not there.
    I want it to stop it's loop whan activecell.value = 107000

    Thanks again!


  9. #9
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    Quote Originally Posted by madmot View Post
    It hangs on it hard.
    How many rows of data you have in your file? It could be because it's still running, not really simply 'hanged'.

    Try and see if adding application.screenupdating helps:

    Please Login or Register  to view this content.
    Edit: just realised what could be the reason, forgot to move to the next cell if it's a blank, sorry about that.

    Edit 2: please use code tags when posting code in the future.
    Last edited by millz; 10-23-2013 at 10:40 PM.

  10. #10
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Yep: Runtime error again.
    Still hanging on when debuggin'

    Please Login or Register  to view this content.
    Should'nt there be a dot in .cells? and Rows? Just a guess like that.

    My file, when fully extend, will have some 108500 rows in the end. This will be a long process.

  11. #11
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    No that Lastrow line is fine.

    I assume you are clicking on the first cell that contains the number before running the macro right? I couldn't understand why was this needed:
    Please Login or Register  to view this content.
    Try changing to:
    Please Login or Register  to view this content.


    Quote Originally Posted by madmot View Post
    Yep: Runtime error again.
    Where is the error highlighting? Post the entire error message as well

  12. #12
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Thanks,

    Yes, i click the first cell of a serie (in a column) that I want the macro to work on.

    (Runtime error '1004': application-defined or objet-defined error) appears in visual basic debugger. Nothing more

    Please Login or Register  to view this content.
    is still highlighted. Lastrow = 0, Rows.count =1048576, sColumn = 19, xlUp = -4162


    Again, my first macro was working perfectly but the problem was: it was stopping at every blank cell. I need it to overpass those blank cells...

    first macro (with french included):

    Please Login or Register  to view this content.

  13. #13
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    Try adding activesheet:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    Book1.xlsxHere's an example attached:

    You see, before G16, it has been done manually with my last good macro. But there are blank cells to deal with...

  15. #15
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Blank cell must not stop the macro

    No luck, same error message, same place.

  16. #16
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Blank cell must not stop the macro

    Ok, realised the problem, sColumn was dim'ed as a string, and was set a number, so Cells taking in a number that's actually formatted as text would give an error.

    I also made some changes.
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-23-2013
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    10

    Talking Re: Blank cell must not stop the macro

    Millz, I went to bed last night before your last post. I'm trying it this morning and I believe it's actually working.

    You are awesome!

    I copied all of your codes and, without any modification, it's doing exactly what it needs to do.

    Thanks again.

    Don't we love Internet?

+ 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] Stop Macro at First Blank Row
    By HappyLadyToo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2013, 10:52 AM
  2. Replies: 0
    Last Post: 07-25-2012, 05:40 PM
  3. Stop Macro If Blank
    By id10t in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-18-2011, 09:51 PM
  4. Stop Macro if blank cells are within certain columns
    By superbob in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-07-2010, 08:45 AM
  5. #DIV/0! how to stop this in blank cell
    By S S in forum Excel General
    Replies: 8
    Last Post: 08-13-2006, 05:50 PM

Tags for this Thread

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