Posts by Rob Xaos

Re: Dynamic named range, ignoring blanks... but on different worksheet?

I think (and without a current version of your Workbook to test with, I am slightly shooting in the dark here) that all you need to do is change your Data Validation to List and then use =Dates as your Source.

If thats not working then please upload a Workbook (having removed/disguised confidential information) and someone will figure it out

Re: Generate multiple worksheets from one worksheet

nea,

Welcome to Ozgrid, and while we are pleased you have been provided with a solution to your problem, please do not pose your questions in threads started by others. Ozgrid forum rules are one question (or close follow-ups) by the Original Poster only. In future please start your own thread and if it helps explain your problem then post a link back to a previous thread.

Thank you.

Re: Failed to hide column with error Message "Cannot shift object off sheet"

You will need to upload a sample Workbook (after removing/disguising confidential information). Please use Edit Post > Go Advanced > Manage Attachments on your previous post.

There is no real way to provide any further help without a Workbook that contains the fault.

Re: Can Excel handle repeatedly creating and destroying controls

There are ways to create multi-page UserForms, however they cannot be 'invisible'. The whole purpose of a UserForm is to contain a set of controls that are used to modify your Workbook/Worksheets. My suggestion was to make it 'Modeless' that means you can have the UserForm open and move around Excel virtually as if the UserForm was not there. That means you can: select cells, select different Worksheets, perform almost any actions you require on the Workbook (although you need to be aware that the UserForm is there and that any of its events could be triggered by changes you make to the Workbook).

I can understand that with 8 years invested in a project that you are reluctant to 'take a different approach' as I phrased it. However you may possibly have to accept that it has outgrown Excel. Some things Excel does exceptionally well, and in my opinion it is one of the most polished of the Office applictions, but handling very huge amounts of data for many different users (which is what this sounds like) is not one of them.

Re: Open Word File and GoTo bookmark

Not an expert on Word VBA by any means but the following seemed to work for me:

Selection.GoTo What:=wdGoToBookmark, Name:="Test"

Although normally I would shy away from using Selection/Select, since in this case you actually want the cursor to change positions then I think that is what is required.

Since I believe there can only be one active Selection I don't think you need to qualify it with a document (in fact I seemed to get a run-time error when I tried).

Re: Open database exclusively

I believe it applies to any database opened in MS Access, however if there are already users connected to (for example) an SQL database then it will not give you exclusivity (since they are already connected). However if you use this on an SQL database with no users currently connected to it then it will be opened in single user mode.

Re: Why use a dollar sign in Mid function

I did a little further checking and there is a slight difference according to this post:http://bytes.com/topic/access/…er-use-if-these-functions

Quote

The difference between the functions with the $ and those without is how
they handle Nulls. The ones with the $ are text only and will give you an
error if you pass them a Null.

If there is any performance difference then it will be negligible.

Re: VB Code (Emulator) for Ms Excel & AS400

Insufficient information to provide a solution.

Please explain in much more detail what you mean by a 'bridge'.

Re: Create new sheet based on cell date

Try:

In Worksheet code module:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    On Error GoTo ErrorHandler
    If Target.Address = "[COLOR=blue]$E$4[/COLOR]" Then
        If Target.Value = Evaluate("DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)") Then
            MsgBox "Today Is the Last day of the Month"
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Format(Date, "mmmm")
        End If
    End If
ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox "Worksheet for this month already exists"
        Application.DisplayAlerts = False
        Worksheets(Worksheets.Count).Delete
        Application.DisplayAlerts = True
    End If
    On Error GoTo 0
End Sub

Just change the address $E$4 to whichever cell you want this to activate for. The code must go in the Worksheet for that cell of course.

Re: Autosort on different worksheet cell change

Uploaded forumhelp.xlsm Workbook has #REF errors in the column F formulas which makes it a bit tricky to test. However I think what you need is:

In Worksheet "New Hires" code module:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = "$B$3" Then
        ActiveWorkbook.Worksheets("Sponsors").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sponsors").Sort.SortFields.Add Key:=Range("F2:F6") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sponsors").Sort
            .SetRange Range("A1:F6")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End If
End Sub

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

Thank you for supplying your solution.

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

I would agree with Stephen that Advanced Filter would be the way to go. It has all the capabilities of COUNTIF(S) criteria and more since you can create AND/OR functions. You might want to use some VBA to build the criteria but the performance of the filter will be much faster than a UDF.

Re: Formula to sum amounts based on Today() or chosen date

Inland,

Welcome to Ozgrid, please note the change made to your thread title. Your orginal title Help with Excel Formula does not help those trying to provide you with a solution nor will it help others find this thread when it contains a solution. Please take this into consideration when you next post.

Thank you.

Re: Resize Table Pasted From Excel,

Bernardes,

Welcome to Ozgrid and thank you for posting an alternative solution in this thread. However in future please ensure that all VBA is wrapped in [noparse]

[/noparse] tags.

Thank you.

Also I would recommend that you remove your email from the posted code. Public forums like Ozgrid are regularly harvested for email addresses by bots and you are likely to become the target to a great deal of spam if your email address remains.

Re: Code to print selected sheets

Simple but perhaps unsatisfying solution:

Sub prnt()
    Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14)).PrintOut
    Sheets(1).PrintOut Copies:=2
    Sheets(2).PrintOut
End Sub

The alternative would be to take the array as a parameter and iterate through it printing each sheet individually. I am not aware of a way to have a collection return multiple instances of the same object (which is what you are asking effectively by passing an array to the Sheets collection with duplicate identifiers).

Re: Multiple criteria lookup

alfa-alfa,

While we welcome you to Ozgrid please do not post new questions in other threads. Ozgrid rules allow one question (or close follow ups) by the original poster only. Please start your own thread and if necessary to explain your issue then link back to a previous thread.

Also, in future please do not requote previous posts unless it is absolutely necessary to explain yourself.

Thank you.

Re: Why use a dollar sign in Mid function

They are just aliases for the same function. I believe that Mid$ is for backwards compatibility with older code.

Re: Count unique entries from columns in 2 sheets.

Leaf,

I think you are not entering the List Range correctly, the use of Old/New/Inventory_new/Price_List_Range have become somewhat muddled as you started off refering to them only as Sheet1 and Sheet 4 so I just took a 'best guess' at what they should be called for the DNRs.

If you take your most recent file and without doing anything else just use: Data > Filter > Advanced filter.
Copy to another location: Selected
List Range: Inventory_new

Change nothing else

Hit OK.

You should get your list.

Re: double quote usage with Evaluate function

The Evaluate method only works for Excel formulas as they would be entered directly into an Excel cell. You are mixing the use of an Excel formula using COUNTA with VBA syntax. You need:

MsgBox Evaluate("COUNTA('" & SourceWB.Worksheets(1).Name & "'!B:B)")

or

MsgBox WorksheetFunction.CountA(SourceWB.Worksheets(1).Range("B:B"))

Re: Sum through a column and group the rows found

I'm glad you were able to find a solution. Please remember that for any future posts you make any VBA code must be wrapped in [noparse]

[/noparse] tags.

Also, public forums like this are constantly scanned by Bots to harvest email addresses. The person who wrote your code for you is unlikely to be pleased if their email address is added to the list of these Bots as a target for Spam. I have therefore removed the email address.