A cell's value human input, other cell values according to another worksheet in the cell values automatically populated
The worksheet below, such as BOM package number/specifications of the field after the human input, he automatically in the work Item in the table to find the corresponding field [FModelSpecification], after finding/material properties, material status fields such as automatic filling, without human input, can't find, don't tube, use VBA to realize the,
1, the material properties of corresponding Item worksheet [FMaterialProperty]
2, the material status in the corresponding Item worksheet [FMaterialState]
CodePudding user response:
This needn't VBA! Use the formula can be completed,The question is: you didn't say where are you going to fill in
CodePudding user response:
Fill into the BOM worksheets,Can you help me realize in VBA, this is just the VBA program I wrote a,
CodePudding user response:
I am to ask you to fill to which a column in the bom!Reference:
Private Sub Worksheet_Change (ByVal Target As Range)
If the Target. The Column=4 Then 'If manual input Column is the Column 4, oneself according to the need to modify the
Dim Rng As Range
"' from the item table D column to find the same as the target of cell
The Set Rng=Sheets (" item "). The Columns (" d: d "). The Find (credit:=Target, LookIn:=xlValues, LookAt:=xlWhole)
If Not Rng Is Nothing Then
"' if you can find, began to fill, with a negative value specified here cell on the left side of the first n cell, if on the right, with positive
Target. Offset (0, 1)=Rng. Offset (0, 1)
Target. Offset (0, 2)=Rng. Offset (0, 2)
"'...
End the If
End the If
End Sub
.
CodePudding user response:
This is a very easy thing,After the problem is, find the data, which column to write ah!
You simply don't see in picture 1 [material properties], [material status] is going to fill to which columns of the BOM table!
CodePudding user response:
You give the link in the attachment, I have no account can't download, I am not interested to register account,Give you a simple example:
1. Suppose you Item FModelSpecification field data in the table is the only; And, from the beginning of the line 2 consecutive storage,
2. In this workbook VBA project, add a form, and then draw a text box, a command button,
3. Add just the form of ShowModal attribute to False (the default is True), otherwise, it's not convenient "continuous fill in data,"
4. Suppose just control is done with the default name (if you change the control name, his name correction in the code below the corresponding control),
5. The code and implementation methods, only supplies the reference, for more details on operation, your own processing,
6. You must be put in the code "column number 1, column number 2, column number 3" to the corresponding numerical (A=1, B=2, C=3, AA=27, more than its column and so on),
7. If you want to read from the Item list in the other columns of data to BOM table, refer to the following code 39 line 9, write their own code,
8. In VBE varies, first add the forms designer window (or its code window) as "active window", press F5 to start the VBA code,
After 9. Start the program, in the BOM list to fill in the data line at any point in a cell,
In the window of the TextBox part number/specifications input data, and then click CommandButton1 realizes the data automatically populated,
The code is as follows:
Option Explicit
Private arrData () As String
Private moSourSht As Worksheet
Private moDestSht As Worksheet
Private mlLastPnt As Long
Private Sub LoadData ()
Dim objSht As Worksheet
Dim strTemp As String
Dim i& , w As Long
The Set moSourSht=Sheets (" Items ")
W=WorksheetFunction. CountA (moSourSht. Range (" V: V))
ReDim arrData (w)
For I=1 & amp; To w
StrTemp=moSourSht. Cells (I, 22). The Value
If (" "=strTemp) Then the Exit For
ArrData (I)=strTemp
Next
MlLastPnt=I - 1
End Sub
Private Function FillData (ByVal iRow As Long) As Long
Dim strTemp As String
Dim lRet As Long
Dim As Long I
StrTemp=UCase $(Trim $(TextBox1. Text))
If (" "=strTemp) Then the Exit Function 'did not enter a valid data
For I=2 To mlLastPnt
If (strTemp=arrData (I)) Then the Exit For
Next
If (I & gt; Then mlLastPnt)
LRet=1 & amp;
The Else
MoDestSht. Cells (iRow, column number 1). The Value=https://bbs.csdn.net/topics/strTemp 'part number/specifications
'[material attribute] :
MoDestSht. Cells (iRow, column number 2). The Value=https://bbs.csdn.net/topics/moSourSht.Cells (I, 20). The Value
'[material status] :
MoDestSht. Cells (iRow, column number 3). The Value=https://bbs.csdn.net/topics/moSourSht.Cells (I, 21). The Value
LRet=0 & amp;
End the If
FillData=https://bbs.csdn.net/topics/lRet
End the Function
Private Sub CommandButton1_Click ()
Dim w As Long
'if the current activity worksheet is not BOM table, does not perform operation
If (Not ActiveSheet Is moDestSht) Then the Exit Sub
'the selected rows belong to the "header" area, does not perform operation
W=ActiveCell. Row
If (w & lt; 4) Then the Exit Sub
'to automatically populate the data
If (FillData (w)) Then MsgBox "enter a project is not found,", 64
End Sub
Private Sub UserForm_Initialize ()
MlLastPnt=0 & amp;
Call the LoadData
The Set moDestSht=Sheets (" BOM ")
End Sub
Private Sub UserForm_Terminate ()
The Set moSourSht=Nothing
The Set moDestSht=Nothing
End Sub
CodePudding user response:
# 3, # 5, I'm sorry, I work BOM table of material properties, material status does not show up, I thought I sent in attachment,