英文:
Printing entire row of table based on changing cell value and then clearing it in EXCEL?
问题
I can provide a translation of the code and relevant parts for your project:
Sub SearchFunction()
Dim querybox As Variant
querybox = Range("B2").Value
If querybox = "machine1" Then
Sheets("ArchiveSheet").Range("A1:D1").Copy Destination:=Sheets("QuerySheet").Range("A4:D4")
End If
End Sub
This code appears to be a simplified example, but it's on the right track. It checks the value in cell B2 of the "QuerySheet" and, if it matches "machine1," it copies the data from row 1 of the "ArchiveSheet" to cells A4:D4 in the "QuerySheet."
You'll need to expand this code to handle multiple machines and implement the clear functionality as well.
英文:
I am making an archive system that allows for a user to search for a machine by business unit, then exact machine. Each machine has its own details associated with it-- all of them put into a table.
The table looks like this:
| Unit | Machine | PC | Software |
The user will insert the unit and machine into a cell within the "query sheet" using a drop-down list, and then press a button. I want the program to look for the machine within the "archive sheet", find the row that the machine is in, and then print all of the details to the query sheet, under the "search bar".
I also wanted to try having another button that would clear the query-- that is to say that would clear the cells with all of the researched information.
I am very inexperienced with Excel, and I have been using VBA to make all of this program work. I'm sure this is simple, but I just need a little bit of guidance on where to go from here.
The story is:
- User enters Unit value in QuerySheet
- User enters Machine value
- User presses button
- Program goes to ArchiveSheet
- Program finds row with machine details within the Unit Table
- Information transfer of that row to cells underneath the "search bar" cells within QuerySheet
- Clear all information associated with query AFTER "clear" button is pressed.
All I could parse is just simply copying it from one sheet to another? But it's completely conditional on the machine picked, and there over 100 machines-- I don't really want to write a program that requires me to do a conditional statement for every machine possible.
Here's an idea that I had, but I don't know if I'm on the right track.
Sub() Search Function
Dim querybox as Variant
querybox = Range("B2")
If querybox = machine1 Then
Sheets("ArchiveSheet").Cells("*stuck*").CopySheets("QuerySheet").Cells("A4:D4")
End Sub
答案1
得分: 1
我建议在你的情况下使用原生的Excel功能，而不是VBA。
例如，对于第1、2款，你可以使用数据验证工具。你可以使用公式从列中提取唯一值。这样，你将有两个包含所有单位和机器的下拉菜单。你还可以使机器下拉菜单依赖于单位下拉菜单的值。在这种情况下，你不需要有一个按钮来处理数据表。
第3-5款是多余的。
第6款：在下拉菜单下，你想要一个选择的数据区域。我认为最好的选择是使用带有下拉菜单参数的筛选功能。
第7款：你可以使用Match函数，同样使用下拉菜单的参数，来检测需要移除的行。使用VBA来移除这一行。
另外，如果你修改了业务逻辑，你可以使用原生的Excel表单来实现你的目标。
希望我给了你另一个想法。
顺便说一句，由于你不熟悉Excel和VBA，可以尝试使用Python。你将更快地实现你的目标。
英文:
I would suggest using native Excel features instead of VBA in your case.
For example, for clause 1,2 you may use Data Validation tool. You may use formula to extract unique values from the column. So, you will have two Dropdowns containing all the units and machines. You may further make machine dropdown be dependent from the Unit dropdown value. In this case you don't need to have a button to address data sheet.
Clauses 3-5 are redundant.
Clause 6: Under dropdowns you want to have an area with selected data. The best choice, to my opinion, is to use Filter function with parameters from dropdown.
Clause 7: you may use Match function, with parameters from dropdowns as well, to detect row, which you need to remove. Use VBA to remove this row.
Also, if you modify business logic, you may use native Excel forms for your purpose.
I hope I've given you another idea.
BTW, since you are not familiar with Excel and VBA, try Python. You will achieve your goal faster.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论