Hi Everyone.
Please help if possible.
I have a Tab "Search Page"Sheet3 that I can either use to open a VB search form or use an on sheet form directly to search on a per circuit or customer basis from the tables, or just to manage the paste and analyse process with buttons. The column values I need to be dynamic, that if I need an additional column added, it would not require a complete re-write. If possible, well documented notes on what the VBA does.
The search form is designed that it would make use of the data tables in Sheet1(CircuitTable) and Sheet2(CustTable) to populate TextBox's. Not yet figured this out.
Ideally I need a dual function in this workbook which would be the buttons method, and the userForm or on sheet form method, which I am having some difficulty with all of it. I have been able to get some of it to work.
The data must be in the Windows clipboard, not Excel. If possible compatible with either.
What is currently working. (Or was)
I copy data from a production system, and paste this into Sheet6 using VBA and xlPasteAll, the sheet is named "Temp" (It's supposed to be hidden), using a macro linked to a button.
I get the message popup that paste is successful and the data is in fact in the sheet.
I also get a message popup if I click Paste Data when the clipboard is empty "Nothing to Paste".
After this I click another button named "Analyze1" or "Analyze2, depending on the data that was pasted, this copies specific columns from the table to a new sheet, where I have VLOOKUP setup in column G and H on customer names to populate the rest of the columns from the table "CustTable". Also another message popup if no data available to analyze. I would prefer the vlookup not to reside in the cells as this can be destroyed by accident. Another method would be better. However if there is no data in the Customer column, a blank would be preferrable, and if there is no match, then "No data" or "No Match". Column G could have no Match, but H may and G will have a default of "No SCP" or blank if no data in the lookup.
Another obstacle is that the source data comes from two different apps, where the column header names are different for the same information. Eg. "Customer" and "Customer Name". I'm strugling with renaming the columns before copying to the new sheet.
There are others too like "Circuit/Eq ID" and Circuit/Equip ID." I am still figuring this lot out. If there is a way to rename the headers after pasting them, or just look for the specific columns and copy the data to the new sheet to analyze using pre-labled columns. Another problem I found is that the columns on the source is user arranged. So they are not always in the same order, however the column names cannot be changed by them. The destination sheet "Analyze" I would prefer defined columns. Also what if my data source is from another spreadsheet?
I am thinking Set somerange as Range(A1:ML) then use an If then statement on the range. Also I need to cater for errors, On Error Resume Next or On Error Goto ErrorHandler.
The sheetform is not currently doing anything as I have not yet started to figure out the coding for searching.(Also do I need to have a separate userform if I am using an on sheet form? Which would be better?). What I would like it to do is populate the information Search onClick. Or popup with a message if not found. This is a lot to ask, but if anyone can help?
I am not allowed to use an Access database. I could install a local copy of mySQL, but that has it's limitations too.
The likelyhood that the customer name or circuit number is not in the table is very possible, which I came across yesterday.
Unfortunately I have to use an excel table as my database as I cannot get SQL database storage at this time. I plan to do this at a future date as a standalone app, with connection to SQL so the database can be maintained seperately from the spreadsheet and app.
The most important part is that this needs to be backwards compatible to Windows 7, as well as Office 2013 and that would include x32 and x64 versions of Windowns and Office.
We are currently migrating to Win10 x64 and Office 365 but there are many people still on Win7 x32 and Office x32.
Any assistance would be appreciated.
I have included what I have been able to piece together. I know it's rather messy, but it's what I can do. I need to make sure this doesn't break.
I've had a couple re-writes for compatibility issues, but there's much that I have not gotten to and it seems complex to say the least. Some things that should work don't and I don't know why either.
I have placed two samples of data from the system into tabs Source1 and Source2. Rows 1 and 2 are my ramblings and are not copied to clipboard from the Apps.
Thanks to anyone willing to assist or take on this challenge. I am figuring it out slowly, but not fast enough.
My full customer list is over 300 000 lines, the cct is about 600 000 lines.
Thanks again. Spreadsheet attached. "Sample data.xlsm"
Bookmarks