Re: Remove duplicates by comparing two pairs of columns in separate tables
Again using an advanced filter, but the formula for the criteria is as follows:
=SUMPRODUCT(--($A2&$B2=$D$2:$D$3&$E$2:$E$3))=0
Note that the two concatenated ranges (highlighted in blue) must be the same size and must cover your list that you wish to exclude. Then select the ranges for an advanced filter as normal.
Attached sample Workbook
Re: Hide Rows based on drop down box
You can try to use the Calculate event but you then need to be careful as to how you detect which cells have changed as a result of calculation otherwise it tends to place a rather heavy overhead on the Worksheet. The alternative is to detect the Change of the Precidents for your formulae and then use that to clean up your formulae. Either way tends to involve a bit of work on your part but can be done.
Re: Dynamic range for Chart to include added values
You would need to give a bit more information. We need to know more about the structure of your data list, what type of chart and which of the types of dynamic range definition you used (there are several listed). I would strongly recommened that you upload a sample Workbook (after removing/disguising confidential information). You can do this by using Edit Post on your original post above and then Go Advanced > Manage Attachments.
Re: VBA help: External Link to CSV file for Access
I did a bit of searching around and the following may prove useful. It comes from http://bytes.com/topic/access/…-link-directory-csv-files
Sub mcrLinkCSV()
Set db = CurrentDb
strDir = "C:\data\"
strTemp = Dir(strDir & "*.csv*")
Do While Len(strTemp)
strFileName = strTemp
strTblName = Left(strTemp, 8)
Set tdfLink = CurrentDb.CreateTableDef(strTblName)
tdfLink.SourceTableName = strFileName
tdfLink.Connect =
"Text;DSN=CSVLinkSpec;FMT=Delimited;HDR=NO;IMEX=2; CharacterSet=437;DATABASE=C:\data\"
CurrentDb.TableDefs.Append tdfLink
Debug.Print strTemp
strTemp = Dir$()
Loop
End Sub
I have not tested this code in any way but it may provide something you can work with. This seems to suggest that it is possible to link to a csv file although other posts I read said that such linked tables are slow and inefficient (which is what I would expect given the original data).
Re: How to export from csv into text file?
Re-reading your original post it is now not clear what you are actually trying to do. You are talking about exporting each row to 'its own array'. Do you mean that for each row of your original data you want to output the multiple lines of text in your original post where the bold text is substituted by the values from the cells for the columns for that specific row?
Re: Cannot Run Shell (VBA) on Some Computers
Its quite difficult to comment on this as much more detail is needed about the circumstances of when your application works and does not. The OS of the workstations and the servers for example. How your application is installed locally or from a server file.
I am not really an expert in this field but I think even one of Ozgrid's volunteers who have more detailed OS knowledge than me would need more information before they could suggest much.
Re: VBA help: External Link to CSV file for Access
I'm not sure what you are asking is possible. An xls file can be handled via VB/VBA object libraries therefore it is possible to link to it and access via object collections and properties. A csv file is simply a text file that follows a particular format. I think the only way to do what you want would be to write your own handler that opens the CSV file as a text file reads any header information and then processes the text file accordingly. However such a process would be very slow.
Re: How to export from csv into text file?
By far the easiest way to do this would be to copy the relevant range to a new Worksheet so that the data you wish to export is isolated then use the SaveAs method for that Worksheet.
Re: How can I show with msgbox then Moving Averages pass which other. VBA code
You will at least need to post a copy of your code (be sure to use [noparse]
[/noparse] tags. I would recommend that you upload a sample Workbook (having first removed/disguised confidential information), use Go Advanced > Manage Attachments to do this.
Re: Syntax for Range with Comma in Formula
There is no way around that unfortunately other than to create your criteria in a continuous range.
Re: Cannot Run Shell (VBA) on Some Computers
Both the suggestions you gave are possible. Security settings could limit the use of Shell and some anti-virus software could detect the use of Shell as virus activity (for obvious reasons). If it was the latter though I would expect the anti-virus software to flag a warning that the activity was attempted.
A lot also depends on what kind of commands you are trying to run.
Re: Syntax for Range with Comma in Formula
To enter a discontinous range you simply surround the range with parenthesis. However quite a few functions cannot use discontinuous ranges, they are simply not coded for them.
You can try:
=DSUM(DataTable,"FTE", (A30,A32))
But if that gives an error then you will need to change how you define your criteria.
Re: Remove duplicates by comparing two pairs of columns in separate tables
Your example does not seem illustrate what you are trying to achieve. In your example the list in A and B only contains unique values, and what I take to be 'What you want' in N & O simply contains a copy of A & B.
Can you explain a bit more carefully what you require or upload a Workbook containing data that illustrates the situation.
Re: VBA code causing Run-time error '1004
As a suggestion to avoid these kind of problems make sure you put:
Option Explicit
At the top of every module. This forces you to define every variable and avoids creating an uninitiated variable via a typing error (as happened to you). It prevents so many hard to debug errors caused simply by a typo.
You can force the VBE (Visual Basic Editor) to add this for you by: Tools > Options > Editor > Require Variable Declaration.
I would say it was the opinion of most of the people who provide support here on Ozgrid that this should always be set.
Re: Hide Rows based on drop down box
Dependent cells (your formula in this case) do not trigger a change event, so that will be the reason.
Re: Extract everything after a symbol and copy to adjacent cell
If your data is simply a single column in A then you don't really need a macro. Just =RIGHT(A1,LEN(A1)-FIND("?",A1)) and Autofill it down column B (double click on the Autofill handle should make it easy).
If you need them as values then after entering the formulas select Column B and then Copy, Paste Special > Values.
Re: Mobile Number Filter ( Formula for begins with in data)
I think the easiest way to do this is with Advanced Filter.
Add a header row to either Worksheet. Then:
A1: Data
I1: Data
I2: Mob*:
Then Data > Filter > Advanced Filter
Fill in the dialog to select all your data columns, the criteria (in I1:I2) select Copy to another location and select where you want your list of Mobile numbers to appear (must be on the same sheet). Then OK.
Re: Web data import into Access - Is it possible?
The answer is 'maybe', it will depend on how your website page is designed and what access you have to it. However if it can be imported into Excel then it is likely (though not certain) that it can be imported into Access.
One simple way is to open the External Data tab on the Ribbon then Import > More > HTML Document. You then need to be able to access your website page as a file (rather than from a Browser).
If you only have access to it via a browser then it may be more problematic. You can try saving a 'snapshot' of the HTML after browsing to it with a Web Browser and then follow the above step, but again this will depend on how the web pages are designed. If they are only rendering the data then no actual HTML for that data will exist.
It is difficult to be more specific without more detailed information about how the web pages are constructed.
Re: how to import multiple values w/ a vlookup into a single cell
If you cross post you must provide a link to the other post (and as a courtesy you should also post in your original thread on the other forum that you have posted here).
I would suggest you create a small sample Workbook to illustrate this situation. While you have a long description (which is helpful) it will be much easier for others to provide a solution if given an example to work from.
Re: Null answer using MIN function 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.