Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Null value Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Re: Null value Excel

    Thanks Stan,
    I went ahead and added the multiple batches.....for the 3 loops (I only showed one) only two of them added any speed.....about a 15% increase overall. Looking at the code, I think that there is nothing more easily done to increase the speed than this in how I am doing it (using some of the other methods Ira has suggested could I know probably such as unrolling the loops--).
    EDIT--unless there is a faster function than word() to obtain the Name in the list within the loop

    Steve,
    The problem with that method is that it will blank out ALL zeroes where I only wanted to blank out those that met certain criteria (the first column having a value). I appreciate the thought though.
    Last edited by MikeC; 12-31-2009, 11:36 AM.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________



    Comment


    • #17
      Re: Null value Excel

      Without testing, an update operation or script would be faster or simply run a script that adds a field to the final table, the one generated after crosstab etc and add the values to that field based on the expression.
      You might be able to do this during the crosstab without the need of additional fields.

      Comment


      • #18
        Re: Null value Excel

        G,
        I attempted to use an Update operation, but it hard codes the fields and the fields change (dependent upon which and how many companies are chosen) . I already have had to create a dynamic Crosstab and a dynamic Join operation, but the Update seemed, at the time, to be enough different that I decided to go the current route/method.

        I do understand about adding the field and an expression and that it may very well be faster.....again, I chose what at the time was easiest for me and the speed is more than fast enough.

        Although a bit of this thread is probably above most beginners, hopefully it will be of some use to others who want to create numeric null values....and also now how to increase script speed a bit. Maybe even how to create a simple loop.

        It was threads such as this that had enabled me to learn a little about how to do such things. I still have the basic loop code snippets in my help file....and leave them there so I can use them as examples for the messageboard when needed.
        Mike
        __________________________________________
        It is only when we forget all our learning that we begin to know.
        It's not what you look at that matters, it's what you see.
        Henry David Thoreau
        __________________________________________



        Comment


        • #19
          Re: Null value Excel

          Mike,

          Don't know if you're going with the copy-paste option for the customer to use, but you could also incorporate an Excel macro in the spreadsheet. Looking at your sample, it appears that there's always a value in column C, whether it's a real number or a zero. I just tested this macro based on the sample from your image:

          Sub RemoveZeros()
          '
          ' RemoveZeros Macro
          ' Macro recorded 12/31/2009 by Jack Ross
          '

          '
          Range("E2").Select

          Do Until ActiveCell = ""
          If ActiveCell.Value = 0 Then
          If ActiveCell.Offset(0, -4).Value <> "" Then
          Selection.ClearContents
          End If
          End If
          ActiveCell.Offset(1, 0).Range("A1").Select
          Loop
          End Sub

          The macro starts by making E2 the active cell, then for every 0 it finds in column C, IF there's an entry in column A, it removes the zero, otherwise leaves it. Then it moves down one row and starts over, until there are no more entries in column C. You could attach the macro to a button in the Excel toolbar, then the customer could simply click it and have it run automatically.

          Jack

          Comment


          • #20
            Re: Null value Excel

            Mike,

            Whoops, sorry, I had put too many columns in my sample. This would be the correct macro:

            Range("C2").Select

            Do Until ActiveCell = ""
            If ActiveCell.Value = 0 Then
            If ActiveCell.Offset(0, -2).Value <> "" Then
            Selection.ClearContents
            End If
            End If
            ActiveCell.Offset(1, 0).Range("A1").Select
            Loop
            End Sub

            Comment


            • #21
              Re: Null value Excel

              Here is a simplified macro and a video showing the macro in action.
              I didn't want to go the macro route thinking pushing a button might be too advanced for some users.
              Other options include DDE or a 3D sheet automatic calculation.
              Code:
              Sub Zero()
              ' Zero Macro
              ' Macro recorded 12/31/2009 by Gabriel'
                  Columns("C:C").Select
                  Selection.Insert Shift:=xlToRight
                  Range("C1").Select
                  ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[-1]=0),"""",RC[-1])"
                  Range("C1").Select
                  Selection.Copy
                  Range("B1:C1").Select
                  Range("C1").Activate
                  Range(Selection, Selection.End(xlDown)).Select
                  Range("C1:C3").Select
                  ActiveSheet.Paste
              '
              End Sub

              Comment


              • #22
                Re: Null value Excel

                Hi Mike,

                A small variant on grayfox5:

                Code:
                Public Sub ClearCell()
                Dim cell As Object
                Columns(3).Select
                For Each cell In Selection
                    If cell.Value = "" Then Exit For
                    If cell.Value = "0" Then cell.ClearContents
                Next
                End Sub
                You can insert this code in the excel file in the same way as described in this post.
                Last edited by Marcel Kollenaar; 12-31-2009, 02:42 PM. Reason: added the link to post
                Marcel

                I hear and I forget. I see and I remember. I do and I understand.
                ---- Confusius ----

                Comment


                • #23
                  Re: Null value Excel

                  G,

                  Not to argue the point...since I haven't done any exporting yet to Excel from Alpha, I don't know if the file has to be created everytime (which I assume it would), in which case the macro button wouldn't be attached to the newly created Excel file. The only way around that would be to attach the macro permanently to the personal.xls file, which is opened every time Excel is, and have a button on the toolbar which would run that macro. As for making it simpler, you could condense my submitted version to:

                  Sub RemoveZeros()
                  Range("C2").Select

                  Do Until ActiveCell = ""
                  If ActiveCell.Value = 0 And ActiveCell.Offset(0, -2).Value <> "" Then Selection.ClearContents
                  ActiveCell.Offset(1, 0).Range("A1").Select
                  Loop
                  End Sub

                  Demo attached.

                  Comment


                  • #24
                    Re: Null value Excel

                    The file doesn't have to be created anew each time but I have no problem putting this macro or any other anywhere you wish. It's just for illustration purpose only.
                    The simplification I am referring to is mass copy instead of going down the cells which might take a little longer if you have 1000's of records but again Excel is pretty fast.

                    Comment


                    • #25
                      Re: Null value Excel

                      Originally posted by G Gabriel View Post
                      I have no problem putting this macro or any other anywhere you wish.
                      I'll try not to take that personally. ;-) Just kidding. Happy New Year!

                      Comment


                      • #26
                        Re: Null value Excel

                        Thanks all,
                        What I have is working, but is always of benefit to see how to accomplish a goal with alternative methods...I have placed everything into my help file for future reference as am fairly certain I will be running across a time when it will be necessary to manipulate Excel in just such ways.
                        Mike
                        __________________________________________
                        It is only when we forget all our learning that we begin to know.
                        It's not what you look at that matters, it's what you see.
                        Henry David Thoreau
                        __________________________________________



                        Comment

                        Working...
                        X