Posts by davegoodo

    Re: A Simple Range Resize Operation

    Thank you! They work a treat, no more error. I really appreciate people like you taking the time out to help others like me. This has answered my question.
    Much appreciated.

    I was just trying to teach myself the resize operation. Instead I got a Compile Error: Invalid use of Property error. I don't understand why, it is a simple piece of code from my own insight. I simply want to add one row to the range. I'll admit that I wanted to add the row above the existing range not below it. I also don't know how to code that into what I'm trying to do. Thanks.

    Public Sub UsingResize()
     Dim G As Range
     Dim i As Integer
     Set G = Range("K4:M12")
     i = G.Rows.Count
         G.Resize (i + 1)
    End Sub

    Re: Referencing Arrays within a Project

    Thanks cytop, a Static Array is just a fixed array in the sense that it has a fixed number of elements in it. What I'm trying to figure out is how I can just put the arrays into the project just once. Then refer to them from 4 or 5 different procedures throughout the project. There must be a better way than just putting the array into every procedure that refers to it, so there becomes multiple instances of the same array in the project. I must be doing something wrong.

    I have encountered some errors, mainly #9 Subscript out of range, OK. I have 2 static arrays in the project I'm developing, and my problem is that I only want to have them sit in one spot within the project. I have tried putting them into a module and referencing them from 4 or 5 different procedures that use them, but I wind up getting the error. What have I done wrong? So, what I end up having to do - which I know is not the most efficient use of the code or the arrays - is to copy the array list of values and paste it into the procedure that is referencing it. So I end up having the same arrays duplicated into 4 or 5 different procedures. My instincts tell me this is not the best way to do this. Surely I can put/store the arrays and their list of values into one spot within the project and reference them from the calling procedure to my heart's content, without the error #9.
    Your assistance would be appreciated.

    Re: Using the Worksheet Change Event

    I understand what you're asking and no the columns were clear. I simply entered 20 into column D5 and E5 correctly showed 20 but F5 showed 40, as if it added D and E. I don't know. I think my lack of knowledge of these events is not helping. In my research I noticed that the construct, If Not Intersect(Target, myRange) is nothing, is quite common and is in your code too. How does that work?

    By the way, it may be best if you sent any code as a text file that I can simply insert into the Excel module. I have specific colour schemes and formats in the file I'm working on, it would be simpler to just have plain text code for the module. Also, I'm currently using the 2nd (or latest) version of what you have sent above. I've just copied the code from that into the code module to replace the existing code in the .xlsm file that I'm using. I'll test that code again to see what happens shortly. Also, Column F, the Stock on Hand Column should just have the basic formula in it, in the worksheet itself, i.e. not in code. The code should just deal with calculations to Columns D, E, G and H.

    By way of clarification, the sheet has approx 220 products, there should be 220 rows of separate calculations for each code. e.g. row 5 has nothing to do with row 6.

    Please let me know if I can help you help me. Thanks.

    Re: Using the Worksheet Change Event

    Thanks Mike. Column A are just product codes. Columns B and C also had product information that I cleared off to simplify things. The only Columns of interest to this exercise are D, E, F, G and H. Nothing else. To answer your question the data for the next inbound order is keyed straight over the top of the previous data, i.e. it is overwritten. Column D has Orders IN where the incoming data overwrites the previous data in that cell. Column E is the order total, this is cumulative. The total is updated each time a corresponding entry is made in the Orders IN cell. Going to the other end of these cells Column H is the Sales cell. It operates in a similar way to the Orders IN cell. A sale is made and the data for that sale overwrites the previous data in that cell. Similarly, the data for Column G is the cumulative total of all sales. It is updated each time a sale is recorded. At the centre of these cells is Column F which is Stock on Hand. This cell has a formula which is: (Column E - Column G) or, in words, Orders received minus Sales gives Stock on Hand (assuming starting inventory is Zero). That is how it operates and how I've been asked to do it.

    I made a post because I haven't used the Worksheet event procedures before and I was having troubles getting things to work properly, which they still aren't. I think the worksheet events are the way to go with this problem. I was hoping someone could get a handle on my situation and help me get through what has been a challenging problem. I'm not as confident with these type of event procedures and need help to understand how they work and specifically how to stop the events happening when I don't want them to. Thanks.

    Re: Using the Worksheet Change Event

    I have written some code for the procedure, the problem is it is too easy to trigger the procedure by accessing other parts of the sheet. I'd appreciate help on how to secure the application from unexpected actions. I've included the code below.

    Re: Using the Worksheet Change Event

    I would definitely agree with you, but unfortunately, I've been directed to have one cell for each row where order volumes can be input and one cell for keying in Sales. I'm stuck with the design specs. There are 5 cells for each row. 2 are the input cells, 2 are the automatic totals and the difference between the two gives the Stock on Hand, cell number 5. That's it, that's what I have. I'm a bit new to using the Worksheet_Change event and it has been giving me some grief, can you help?

    Re: Using the Worksheet Change Event

    OK, I looked up how to attach a file. I've attached an example file. The code is in the sheet's code. The business rules apply as I've set out in this post so far. I have deleted product information in columns B and C and a product code remains in Column A. The use of Worksheet_Change does the job but there are a lot of things that can happen which can trigger the code into action without wanting it to be. The user really only needs to use columns D and H to key in data, there rest is covered by formulas and code. Thanks.

    Re: Using the Worksheet Change Event

    Hi Mike,

    Unfortunately those formulas don't quite do the job. Each of your formulas gets overwritten by the next order. My mistake, I didn't emphasise this sufficiently when setting out the problem the first time.

    The requirement for each row is that it is "self contained" for each product. I am using each row to represent the movements of stock, the following are the exact addresses:
    Columns A, B and C contain product codes and other information. Column D is the input cell for orders received for product X. Column E is the Total, it increments by the amount keyed in Column D for product X. Each new order received overwrites the last order received for product X. But, each order adds to the Total in Column E. Sales does the opposite. Column G is the input cell for unit sales of product X. It overwrites the last sales value added for product X. Each entry for sales adds to the Total Sales column. Finally, stock on hand is shown by the difference between Orders Received Total and Total Sales.

    Sorry for giving out unclear details, I hope this info helps. Now I'm trying to use Worksheet Events Change event to calculate new Totals. Thanks.

    I'm working on a sheet where I'm trying to use the worksheet_change event. I'm trying to use the change event to run the code that updates Total Stock In & Out as well as Stock on Hand.
    Specifically it is a Stock Control system. There are 5 columns: 1) Stock In (Orders), 2) Total Stock In, 3) Stock Out (Sales), 4) Total Stock Out, 5) Stock on Hand.
    To accomplish say, stock orders IN I have tried to use a Paste Special xlAdd to add stock orders to total stock, the business rules for each columns are:
    1) Stock IN and 4) Stock OUT are keyed by the user,
    2) Total Stock IN and 4) Total Stock OUT need to be calculated as cumulative columns, e.g. Total Stock in (Now) = Total Stock in (Previous) + Stock In (Now) ; This is the same type of calculation for the Total Stock Out (Sales) column.
    3) Stock reduced by the Amount of Sales or Stock Out,
    4) Total calculated as per 2) above.
    5) Stock on Hand is calculated as Total Stock IN - Total Stock Out

    It seems simple enough but I'm having all sorts of unexpected results occuring with the Change event and running the code, any suggestions please?

    Re: Using error 1004 as a signal to take action - Excel 2010

    Thanks for your help. I was just in the midst of editing my post because I'd made a bit of a mistake in what I had been doing and thereby posting. What I mean is, the fact that I hit the bottom of the sheet through the Range.End(xlDown) method hadn't triggered the error 1004! Instead it was my attempt to do another offset and delete the entire row when I was already at the bottom that had caused the error. I was confused that there was some type of error that occurred when hitting the bottom that I could use to signal the end of my data processing. Once I realised that I could simply use range end down to go to the bottom and I tested if the activecell.row was >100,000 then that worked fine. So all is well. Sorry to muck people around. I greatly appreciate your getting in and giving a reply to this puzzle, thanks very much. I'll leave the post as it originally stood now.

    I have a sheet with several blocks of data in it. My code is to simply go down the sheet from block to block and delete the blank lines between each block. (I'm preparing the data to be one big block that I can import into Access. The issue is when I get to the last block of data, the Range.End(xlDown) statement hits the bottom of the sheet and the Error 1004 Application Error is triggered. When I get to the end of the last block of data I want to end the procedure and Exit Sub. I have searched online and found an article which said I could use "On Error Resume Next" to "handle" the error but I couldn't get anything to happen other than the error message box (Error 1004 Application etc.) appearing.

    blnBottom = False
            On Error Resume Next
            ActiveCell.End(xlDown).Offset(1, 0).Select
            If Err.Number <> 0 Then
                MsgBox "End of Data Input Area", vbCritical
                Exit Sub
            End If
            Range(ActiveCell, ActiveCell.Offset(3, 0)).EntireRow.Delete

    I have shown my attempt to "handle" the error in the code snippet above. All I want to do is exit the sub when I've reached the bottom of the last block of data. I've tried a few different variations on the theme but nothing has worked. Can you help please?

    Re: Edit Cell to Remove unwanted numeric characters

    Thanks for getting back to me. Fortunately I had another look through my programming and found what was causing it. Sorry, I made a mistake, the use of "Selection" meant the location of the ActiveCell had moved and this had implications for controlling where the procedure ended. Because of this it created an infinite loop. It is working OK now. Thanks.

    Re: Edit Cell to Remove unwanted numeric characters

    Hi, you shared some regular expression code with me in a post the other day. I ran the code in a slightly different context today and it wound up in an infinite loop.
    The block of code I used was:

    I made your code into a function called RemoveNumeric() It is below:

    I ran my code today and it finished up in an infinite loop, do you have any ideas as to why this could have happened?

    Re: Stop Excel converting numeric values to dates

    I haven't tried to format the cells as Text before importing no. If I was to do that using VBA, what is the formatting code for "Text"? In fact, what is the syntax? I have done .numberformat = "##0.0" for example but I've not needed to do Text, what is the property that goes before the format assignment? Because the data imported is variable in dimension, sometimes 1500 rows and 20 columns is it, or, there may be 800 rows and 15 columns it is not often the same dimensions twice. The implication of this is that I am not always able to stipulate in advance which cells are going to be cells and those that aren't. (Unless there is some Clipboard technique that detects the rows and columns "on the fly"?)

    I endeavoured to overcome these problems by switching to the alternate download format, namely 2) "W%P%S". In this way I down loaded the percentage Wins, the percentage place and Starts remained as an integer. I could then reconstruct the 1) "WPS" format by multiplying the Starts by each W% and P% respectively, ending up with the "WPS" result via that extra series of calculations. The problem became, that Excel would begin to convert column values that looked promising back over into dates again and I was once again back at square one ... again!

    I need to establish a process that moves from the initial download and preparation of the Worksheet down through intermediate calculations and finally producing a result. A major part of guaranteeing the foundation of this process is to prevent Excel from doing unexpected or unwanted things with the data and simply follow the instructions given to it in a consistent and stable manner. We can hope.

    Re: Stop Excel converting numeric values to dates

    I would like my next approach to be that Excel doesn't convert anything to dates. That is the problem. I know which columns are going to be used and what format each of those should be. Prior to pasting any data I will be running a Macro and hard formatting each cell and range to the expected or planned format. I want to avoid leaving myself open to having to rework large numbers of "rogue" cells. There are literally 000s of cells to deal with here so I need to have automated solutions where possible.

    Any columns that remain unformatted and in their original form such as "W-P-S" or "2-6-12" (which, by the way, is 2nd June 2012 in Australia) I use the texttocolumns function, which does the job quite well. Anything else that remains unformatted , I will need to address in a single sweep. I can't afford to undertake individual cell editing, too many cells! But I have certainly noted your contribution and extend my thanks to you for that.