Posts by GeorgS

    Re: Replace Cell Address In Formula With Its Named Range


    I edited the post so that you could see where I found my solution.
    Yours looks good for big workbooks.

    Can't tell you how much it hurts to have my thread count increase by 50% when the solution requires 3 words.


    Hi All,

    Is it possible to update or refresh an existing formula to reference a named range that was created after the formula.

    I am thinking of a simple situation like this:
    1. Create formula in Cell A1: =B1+C1
    2. Define the name for Cell B1 as "SalesJune"

    Now the formula in Cell A1 should read: =SalesJune+C1
    Sounds simple doesn't it? I don't know how to do it or if it is possible.

    Note that this is 2003 SP2 XP. I have seen solutions in Vista.

    Background story is I want a Summary worksheet to reference the 12 monthly worksheets behind it. Note that this is a small amount of data, that is entered in a confusing way for each month, so having a table of data that can then be drawn into a pivot table does not apply.
    I intend to create a template worksheet for each month, create the formulae to reference these worksheets quickly while the order (on each of the month worksheets) is preserved. Then name all of the ranges for the cells (from their labels) that feed into the summary page so that the formulae make sense.

    Hope that makes sense.



    Re: All Possible Permutations For Defined Numbers


    Make a table "MyTable" in Access with a single field "MyField".
    Populate that table with the numbers 1 to 24.
    Make a query and add MyTable to it 4 times.
    Insert the field "MyField" from each of the 4 tables.
    The designer should now look a little bit like this:

    MyField MyField MyField MyField
    MyTable MyTable_1 MyTable_2 MyTable_3

    Do not join any of the tables.

    Run the query.
    Go to the last record and you should see the navigator bring up 10626.


    Re: Run Multiple Access Queries

    Hi Forrest, and welcome,

    I'm guessing that you want to "stack" the results of your queries on top of one another?
    I'm also guessing that the format of these queries is exactly the same: Same number of columns and same type of data in each of the columns?

    If this is the case then you can run a UNION query through MS Query without using VBA. These can be a little bit slow, so it may not be the correct solution.


    Re: Prevent Text Being Split Down Rows On Import

    Whoa there Jake,

    More than one way to skin a cat:

    I am working from your example:
    1. Paste the above code into a new module in your workbook
    2. Put your cursor in cell c3 on sheet1
    3. Run the "concatenator" macro from the tools/macro menu

    Note: it's a bit patchy - it doesn't do the last line.

    Re: Fill A 2-d Array

    First off, as you will have gathered, I was wrong when I said this:


    One of the things that is going wrong is that you are trying to fill your 2 dimensional array with 5 dimensions, (and within each of the 5 dimensions there is another array with 4 dimensions!).

    I think that mikerickson and jindon have finished this off.


    Re: Multiple Cell Combining


    This problem happens because of the way that you are importing the data into Excel, and has something to do with the carriage return character (the character that get entered when you hit return).
    Your import procedure/method is incorrectly recognising the carriage return as an instruction to begin a new line.


    Re: Set Multiple Conditional Formats Based On Criteria


    I just had a quick check of conditional formatting - it doesn't look like it can be done from there.

    If your combo boxes are of the embedded control type and not the validation type, then it would be pretty simple to put some code around this, once you have named the range that you want to apply the formatting (as long as this range didn't change too much).


    Re: Manually Filling A 2-d Array

    Sehr Geehrte Andy,

    First off, I will say that your post is a little bit hard to read, so please forgive me if I am oversimplifying

    First of all, a 2 dimension array is really just a grid (or a matrix), where the items in the brackets are the co-ordinates of the values within that grid.

    You have initialised your array with 2 dimensions

    arrTrans(1 To 5, 1 To 4)

    - so within your grid there are 5 rows and 4 columns.

    When you fill the array, you need to observe this row/column format.
    One of the things that is going wrong is that you are trying to fill your 2 dimensional array with 5 dimensions, (and within each of the 5 dimensions there is another array with 4 dimensions!).

    Array("B1", 2, strCellNameRng, strSig), _ 
    Array("D1", 1, .Range(strOrgaInfoRng).Cells(2).Address, strSig), _ 
    Array("E1", 1, strCellRunNRng, strSig), _ 
    Array("I1", 2, strTypeRng, strSig), _ 
    Array("G1", 2, .Range(strOrgaInfoRng).Cells(3).Address, strSig)

    Another problem with your code is the unqualified reference


    Here the code witll not compile because there is no indication of what object the


    belongs within

    Perhaps you could tell us a little bit more about what you are trying to do?


    Re: Reached Maximum No. Of Controls On Access Form


    One form
    One combo box for selecting the child's name
    One list box containing the 100 questions.
    One option group containing labels for the 3 standard responses.

    Loading the form loads up the combo box and the list box.
    Select the child from the combo box.
    Clicking on the list box selects the question, and loads the labels in the option group with the appropriate comments.

    Selecting the option in the group box writes the response to the database for that question, for that child (and should probably move to the next question in the list box).


    Re: Dynamically Create A Udf Array To Return Sql Records


    I vaguely recall someone having a much better way to do this. Here is my brutish version:

    Have a good weekend!


    Re: Long Vs Integer



    George, why do you believe readability is sacrificed? So long (no pun intended) as good variable naming conventions are adhered to

    I should have worded that better. From:


    avoiding using integers removes a level of validation and code readability



    avoiding using integers removes a level of validation and a level of code readability

    I'm guessing that you are talking about Hungarian notation?
    Without wanting to side track into a naming convention debate, I found that I can fit 95% of my functions into one screen, and so, after using it for about 10 years, no longer needed Hungarian to tell me the type of the variable.
    (I droned on a bit here)

    So, readability bit comes in here:
    (Once again, not an example of which I am proud.)

    Dim seatCount as Integer
    Redim stadiumSeating(seatCount)

    I know that we are talking about the WACA and not the Camp Nou.
    This example is getting more ludicrous as I go a long, so think that I will stop.


    Re: Long Vs Integer

    I hadn't heard of that "rule of thumb"

    My only small objection to this is that avoiding using integers removes a level of validation and code readability. Nowadays speed is less of an issue than readability.

    I'd be (mildly) confused if I came across some code that dimensioned (struggling to come up with a good example here) the seats in a 20,000 person stadium as Long.

    I guess as the number of rows in an Excel worksheet grows the Integer becomes less useful.


    Re: Dynamically Create A Udf Array To Return Sql Records


    This is straight out of my MS Access "toolbox"

    I've got one in ADO. Net as well, but it is based on an open connection and a separate ".ExecuteReader" based function, so it is a bit difficult to disentangle.


    Re: Automate Or Schedule Backups



    Access has to be opened for it's code to work.
    If you have an IT dept. then this is the time to call them.
    Otherwise, Windows XP has a Backup utility - do a search in Start/Help and support.