Posts by GeorgS

Re: Replace Cell Address In Formula With Its Named Range

mikerikson,

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

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

G.

Re: Update Formula For Named Range Reference

Oh, Somebody please kick me.
The basic solution is named ranges here
http://www.ozgrid.com/Excel/named-ranges-constants.htm

Still interested in hearing ideas for the background story.

Regards,

G.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]mikeerikson,

Yes, as the rest of my initial post details, the Summary worksheet references the 12 worksheets behind it.

G.

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.

Thanks,

GeorgS

Re: All Possible Permutations For Defined Numbers

Leoworth,

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.

G.

Re: To Write Vba Program To Stack Data A Specific Way

mps161,

Post a sample so that I can create some matching vba.

G.

Re: Combine Worksheets And Add Worksheet Name To Data

sostroff,

This requires some specific programming, so I think that you are going to have to post a sample of your data.

G.

Re: Create Combination Of Permutations For Defined Number Of Sets

Leoworth,

If you have MS Access then this is very simple. Do you have MS Access?

G.

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.

G.

Re: Multiuser Environment Tips

iwrk4dedpr,

Here is a good program for logging errors here.
(Thanks, Allen Browne)

G.

Re: Prevent Text Being Split Down Rows On Import

Whoa there Jake,

More than one way to skin a cat:

Public Sub concatenator()
Dim header1 As String
Dim x As Long
Dim startRow As Long
    
    Do Until ActiveCell.Offset(x, -1).Value = ""
        If ActiveCell.Offset(x, -2).Value = "" Then
            header1 = header1 & " " & ActiveCell.Offset(x, -1).Value
        Else
            ActiveCell.Offset(startRow, 0).Value = header1
            startRow = x
            header1 = ""
            header1 = header1 & " " & ActiveCell.Offset(x, -1).Value
        End If
        x = x + 1
    Loop


End Sub

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

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

Re: Fill A 2-d Array

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

Quote

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.

G.

Re: Multiple Cell Combining

Jake,

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.

G.

Re: Set Multiple Conditional Formats Based On Criteria

mattenborough,

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).

G.

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(
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

.Range(strOrgaInfoRng).Cells(2).Address

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

.Range(strOrgaInfoRng).Cells(2).Address

belongs within

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

G

Re: Reached Maximum No. Of Controls On Access Form

brendanj,

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).

G.

Re: Dynamically Create A Udf Array To Return Sql Records

Mattheq,

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

Private Function DumpData(ByRef ws As Excel.Worksheet, ByRef vaData As Variant) As Boolean


Dim x As Integer
Dim y As Integer


    For x = 0 To UBound(vaData, 1)
        For y = 0 To UBound(vaData, 2)
            ws.Cells(x + 1, y + 1).Value = vaData(x, y)
        Next
    Next


End Function

Have a good weekend!

G.

Re: Long Vs Integer

Dave,

Quote

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:

Quote

avoiding using integers removes a level of validation and code readability

to

Quote

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.

G

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.

G.

Re: Dynamically Create A Udf Array To Return Sql Records

Mattheq,

This is straight out of my MS Access "toolbox"

Public Function LoadTwoDimArray(ByVal ssql As String, ByRef av As Variant, ByVal sSource As String) As Boolean
    
        On Error GoTo errorhandler
        LoadTwoDimArray = False
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim fld As DAO.Field
        Set db = CurrentDb
        Dim x As Integer
        
        Set rs = db.OpenRecordset(ssql, dbOpenForwardOnly)
        
        With rs
            
            If .BOF Then
               ' MsgBox "It was not possible to load the " & sSource & " array.", vbInformation + vbOKOnly, csAppName
                LoadTwoDimArray = False
                Set rs = Nothing
                Set db = Nothing
                Exit Function
            End If
            'Debug.Print ssql
            Do Until .EOF
                ReDim Preserve av(rs.fields.Count - 1, x)
                'Debug.Print rs.Fields(0).Value
                For Each fld In rs.fields
                    av(fld.OrdinalPosition, x) = fld.Value
                Next
                .MoveNext
                x = x + 1
            Loop
        End With
        LoadTwoDimArray = True
        'above: must be true if we have got to this stage
exit_errorhandler:
    Set rs = Nothing
    Set db = Nothing
    
    Exit Function


errorhandler:
        MsgBox "Database Error:" & vbCrLf & Err.Description & vbCrLf & _
        "Error Number:" & vbCrLf & Err.Number & vbCrLf & _
        "Source: " & vbCrLf & "LoadTwoDimArray", vbExclamation + vbOKOnly, csAppName
        LoadTwoDimArray = False
Resume exit_errorhandler
    
End Function

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.

G.

Re: Automate Or Schedule Backups

abundant_lyfe,

No.

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.

G