Hello
I'm looking to update or input a new entry into an equipment table that I have put together for some maintenance forms. Basically these equipment tables need to be updated periodically to update any new information associated with the device (such as certification dates & tolerances), but the unique identifier of the device should not change (e.g. unique name/ID given to the device).
Quick headsup. I don't believe Data Forms will work as more than 1 of the equipment tables/lists exceeds 32 columns of data (btw, there are multiple tables for different types of equipment that are far too different, so there will be multiple entry forms & macro/VBA code for multiple tables)
The idea is to have one worksheet that's serving as the update form to search for and replace the contents of the table on another dedicated worksheet with the equipment list/table. If the equipment doesn't exist, it'll be treated as a new entry (through the same form).
Just an FYI, I may already have code for the new entry part of it (enter info in one form/worksheet, macro button and code to enter into a seperate worksheet), as the main purpose of this workbook is to actually capture maintenance events, so there has been a macro already developed (or found via Google searches, lol) and seems to work great. Not to say that this is what needs to be used, but I'm wondering if I can build off of this.
Below is the code that has been used on several different forms and tables for the new entry feature (FYI, this code/feature for the existing code will not be changing, as it already works and works quickly, but if this code doesn't work for this new task, I'm all ears for different code for this one task).
What I'm wondering is if this code can be manipulated to accomodate both the new entry and the VLOOKUP/find/replace type of feature I'm looking for.
As you can see the "next_row" variable is set to the next blank row in the output table/worksheet. I'm wondering if it's possible to point two different outcomes through and IF statement to the "next_row" (which may be renamed to something like "new_entry"). Basically if there's a match, then set "new_entry" to the row number from the match, and if there's no match, then just set "new_entry" to the same code as "next_row". This way this output portion of the existing code will suffice for both the match & new entry.
The IF statement piece of it seems easy enough, but I'm not so confident in being able to figure out the search function with a resulting boolean to coax the IF statement to set the "new_entry" to the update form/worksheet as the source of data.
I apologize if a lot of that was lengthly and nonsense. I'm not really an Excel/VBA guy so I only know very basic stuff.
Thanks ahead of time for any input!
I'm looking to update or input a new entry into an equipment table that I have put together for some maintenance forms. Basically these equipment tables need to be updated periodically to update any new information associated with the device (such as certification dates & tolerances), but the unique identifier of the device should not change (e.g. unique name/ID given to the device).
Quick headsup. I don't believe Data Forms will work as more than 1 of the equipment tables/lists exceeds 32 columns of data (btw, there are multiple tables for different types of equipment that are far too different, so there will be multiple entry forms & macro/VBA code for multiple tables)
The idea is to have one worksheet that's serving as the update form to search for and replace the contents of the table on another dedicated worksheet with the equipment list/table. If the equipment doesn't exist, it'll be treated as a new entry (through the same form).
Just an FYI, I may already have code for the new entry part of it (enter info in one form/worksheet, macro button and code to enter into a seperate worksheet), as the main purpose of this workbook is to actually capture maintenance events, so there has been a macro already developed (or found via Google searches, lol) and seems to work great. Not to say that this is what needs to be used, but I'm wondering if I can build off of this.
Below is the code that has been used on several different forms and tables for the new entry feature (FYI, this code/feature for the existing code will not be changing, as it already works and works quickly, but if this code doesn't work for this new task, I'm all ears for different code for this one task).
What I'm wondering is if this code can be manipulated to accomodate both the new entry and the VLOOKUP/find/replace type of feature I'm looking for.
As you can see the "next_row" variable is set to the next blank row in the output table/worksheet. I'm wondering if it's possible to point two different outcomes through and IF statement to the "next_row" (which may be renamed to something like "new_entry"). Basically if there's a match, then set "new_entry" to the row number from the match, and if there's no match, then just set "new_entry" to the same code as "next_row". This way this output portion of the existing code will suffice for both the match & new entry.
The IF statement piece of it seems easy enough, but I'm not so confident in being able to figure out the search function with a resulting boolean to coax the IF statement to set the "new_entry" to the update form/worksheet as the source of data.
I apologize if a lot of that was lengthly and nonsense. I'm not really an Excel/VBA guy so I only know very basic stuff.
Thanks ahead of time for any input!
VBA Code:
Sub Update_Database()
ws_output = "Equipment_Database"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("C4").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("C5").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("C6").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("C8").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("C9").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("C10").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("C11").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("C12").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("C13").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("C14").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("C15").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("C16").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("C17").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("H8").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("H9").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("H10").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("B20").Value
Sheets(ws_output).Cells(next_row, 18).Value = Now
End Sub
