Posts by Rob Xaos

Re: Null answer using MIN in Index Match

Two threads concerning the same issue running in parallel. Both now locked until OP contacts a moderator to have one unlocked. The other will then be deleted.

Re: autofilter not working correct for date

tjsingh,

As AAE mentioned you need to attach your sample Workbook. Please use Edit Post on your original post and then Go Advanced > Manage Attachments. It is not really possible to examine this without your data.

Re: Remove duplicates by comparing two pairs of columns in separate tables

This is not a follow on question. The situation is entirely different (although I can see why you thought it was similar). Always start a new thread unless you are absolutely sure that the question is very closely related to your original question. This time I have moved your post to a new thread. In future the original thread may simply be locked or the new post deleted.

This time you have two separate tables and you are trying to eliminate duplicates based on the two tables. The solution will be somewhat different.

Re: Runtime Error 1004

Is "Recipe" the correct name of the sheet? Maybe it should be "Receipt" ? It either has to be the sheet name or xrow. There needs to be a value in A1 and A2 otherwise:

xrow = Range("A1").End(xlDown).Row
xrow = xrow + 1

Will result in xrow being set to last possible row + 1 which will cause 1004 run-time.

Re: How to match multiple criteria and return multiple values

Take a look at the formula in #4 of Multiple criteria lookup
Another example in Multi criteria match, including date range with multiple return

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

Thread on this issue has been opened in HIRE HELP so closing this thread.

Re: Accessing file after program run from Shell completes

I think the issue may be that you are not completely adhering to the solution provided by MS. Their intention is that the Shell'd program creates a flag file when it has completed and then the VBA tests for the existance of that flag file.

You are testing to see if the file created by your Shell'd program can be copied (available for output) that does not guarantee that the file is available to open for input.

While not a very elegant solution (these things often are not), you can try:

Shell ("mprog.exe outfile.txt")
 
On Error Resume Next
Do
    Open "outfile.txt" For Input As #1
    If Err.Number = 0 Then Exit Do
    DoEvents
Loop Until False
On Error GoTo 0

Re: VBA function does not update when values change on Worksheet

Dtms1,

While we welcome you to Ozgrid and thank you for providing a solution to your own problem which will help others in a similar situation. In future please take care to create thread titles that use Search Friendly Key Words. Your original title Any idea around this? would not have helped you find a thread that contained an answer and would not help another person with a similar problem find your solution.

Re: Excel OCR Add-In

Just to mention quickly that MODI is not installed by default so you may need to add it in with your 2007 installation process. Despite it being an excellent package with reasonably good VB/VBA integration it has been depricated in Office 2010.

Re: USD $50.00 Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

My apologies to both GoCavs and Parthasarathi,

I had fully intended to provide a solution and was working on it. However, as Parthasarthi correctly pointed out I did not provide a time frame after my acceptance. Under the circumstances I will thefore step aside and leave Parthasarathi to complete it.

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

Forum rules do not permit me to handle attachments for posts in the Free Help section of Ozgrid forums via PM. The purpose of this section of the forums is for open discussion of a problem and the provision of a public solution. If you require direct assistance in this way then you will need to open a thread in HIRE HELP

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

GoCavs,

Your last code post has got badly mangled and as such is unreadable. Can you please use Edit Post and re insert it.

Are you asking that this code is used or is this now a solution so you don't need this done?

Re: $50 USD Paypal: Allow Sorting, Filtering, Grouping on Protected Sheets

I will look at this

Re: Converting between date types: "20101012"

Test that they are actual DateTime values by widening the column, and remove any alignment on that column. If they are DateTime values then they should be right aligned (as a number).

If they are then you can simply use Format > Cells > Date and pick the format you require.

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

I assume that 58kb is 580kb (since you could upload 58kb). Unfortunately the forum limit is ~105kb. You can provide a link to an external storage site though.

Re: Filter and copy

Elric,

Ozgrid posting rules are one question (or close follow-ups) by the Original Poster only per thread. Please start your own thread and link back to a previous one if necessary.

Thank you.

Re: Create new sheet based on cell date

I'm sorry I am a little difficulty understanding what you need.

Is Calculation a function you are writing?
Do you need a copy of the first sheet (formulas and data) made onto the newly created Worksheet?

Re: to oz menbers

Yes Junho, it is definitely real, it shows you as an OzMVP under your name.

Congratulations!

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

The 'Dates' I was refering to was your named range from the previous thread. I have redefined it as:
Dates, Refers to: =OFFSET(Lookups!$A$2,0,0,COUNTA(Lookups!$A$2:$A$32),1)

Your reference above was to an empty column which will result in an empty range - hence the error.

See attached workbook where I have implemented this for your two date columns. You should be able to work out how to extend that to your other validation drop-downs.

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

I think what you are asking about is directly related to communication with the AS400. Although someone here may have knowledge of that, it is somewhat beyond the scope of this forum. You would be better off looking at: http://publib.boulder.ibm.com/…ks/html/host_access08.htm which has information directly related to this kind of communiction problem.