Log In
Register
Home
Active Member
November 24, 2020
Offline
I need a code that will find the list of ID’s on “Nucomat-Dashboard” sheet in cells K4:K36 in the “Received” sheet and move them to the “Completed” sheet under the appropriate column depending on which button is pressed on the “Nucomat-Dashboard” sheet. The formatted ID is one column to the right of the ID that will need to be moved.
Currently, the code copies the list and pastes it in the next available cell under the correct column on the “Completed” sheet however I really need it to move them from the “Received” to “Completed” sheet to prevent the “Received” sheet from having outdated information.
Then anyone can locate a sample by clicking on the “Sample Finder Magic Machine” button which brings up a user form to fill out. This part works great so I just need a way to move the ID’s from one sheet to the other instead of copy and paste.
February 20, 2020
Offline
Hello,
I need more details to complete the macro
at this point I have already built a macro that searches all Worksheets ("Received") based on your Worksheets data ("Nucomat-Dashboard") in range ("K4: K36")
the results of my macro were:
range address: $G$46
2,350xxxxxxT0050201006111022020005FD#
range address: $G$49
5,350xxxxxxT0050201006111022020026#
range address: $G$50
6,350xxxxxxT0050201006111022020003#
range address: $G$51
7,350xxxxxxT0050201006111022020006#
range address: $G$52
8,350xxxxxxT0050201006111022020007#
I need to know:
are the IDs unique or can there be the same IDs?
in Worksheets ("Received") the same ID can be in more than one range or only in one (I defined my macro to find only the first match)...?
with all occurrences and correspondence to 100% (view images)
once found the IDs is to copy (example of the IDs found by my macro) Range of columns ("G" & "H" & "I" & "J") or just Range of columns ("G" & "J" ), is that the column ranges ("H" & "I") are hidden ... copy that data to Worksheets ("Completed")?
and after copying, is the data found in Worksheets ("Received") to be kept or deleted?
Miguel,
Active Member
November 24, 2020
Offline
This is great! I very much appreciate your help. The ID's will de unique and only appearing once in the "Received" sheet. Once they are found in the "Received" sheet they should be deleted and the empty cells removed so the remaining data can move up. Does that make sense? I believe the code needs to remove both columns for each found ID since the left column has the original ID and the right column has the formatted version. I am looking forward to your response as I have been trying to find someone to help me on this for a very long time. Thank you so much
February 20, 2020
Offline
Hello,
if the item searched in Worksheets ("Received"),
is found in column ("E") the value of column ("B") and column ("E") should be copied,
<< columns "C" & " D "does nothing >>,
to which column in Worksheets (" Completed ")?
then just delete the cells in the columns ("B" & "C" & "D" & "E") that had the data
and if the item searched in Worksheets ("Received"),
is found in column ("J") or ("O") or ("T"), is the same question, where does the data go? to which column in Worksheets (" Completed ")?
I apologize for asking, but there are colors in the columns of Worksheets ("Received") different from Worksheets (" Completed ")
and the header names are different
Worksheets ("Received"): and not counting the hidden columns
Unit 5
Unit 6
Unit 7
Manual Digestion
Worksheets("Completed"): and not counting the hidden columns
Manual Time
Herzog Time
LECO Time
AXN Time
Line 5 Time
LIMS Co's Time
Miguel,
Active Member
November 24, 2020
Offline
You are correct about which columns the data gets deleted from. Once the user scans in the ID's they will choose which column the ID's get sent to on the "Completed" sheet by clicking one of the buttons above where they scanned in the ID's. For example if they send those samples to Manual then they will click the Manual button and the ID's will be pasted into the column with the name Manual on the "Completed" sheet. Does that make sense? That way when someone wants to search for a sample it will show where it was sent to.
February 20, 2020
Offline
Hello,
I added a new userform (UserForm2), it is with him that you will do the tests and check if this is what you want, you need to adapt part of your codes to my macro
I added two new modules, with several macros: (cod_for_findAllMatches) & (cod_for_range_cells) you will check that there is a sheet with the name ("Received (8)"), is a copy of Worksheets ("Received"), before performing a test, you must copy the sheet, make tests, delete the test sheet, rename the copy with the name Worksheets ("Received")
do not execute the part of your code that cleans the cells in K4: K41 ...
do the tests, and if everything goes as expected, then yes, add whatever you want to complement my macro
I deactivated your code: Public Sub Worksheet_Change(ByVal Target As Excel.Range) in Worksheets("Completed")
it crashes with my macro when copy & paste
I attached a file, there were a lot of codes and long codes to paste here
Miguel,
1 Guest(s)
