VBA 当sheet1A1的值在sheet2A列出现时A2开始显示sheet2所对应的数据...

发布网友 发布时间：2024-10-23 22:29

我来回答

共4个回答

热心网友 时间：2024-11-01 13:52

这个很简单，再sheet1的worksheet_change事件中监视A1的值，然后在sheet2中查找，找到后就显示，找不到就不显示。 代码如下：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Integer, m As Integer
i = 1
m = 2
If Target.Row = 1 And Target.Column = 1 Then
Sheet1.Range("A2:G1000") = ""
Do While Sheet2.Range("A" & i) <> ""
If Sheet2.Range("A" & i) = Sheet1.Range("A1") Then
For j = Asc("A") To Asc("G")
Sheet1.Range(Chr(j) & m) = Sheet2.Range(Chr(j) & i)
Next
m = m + 1
End If
i = i + 1
Loop

End If
End Sub

热心网友 时间：2024-11-01 13:48

在 A1 输入数据后回车，让 A2 被选中激发填充代码。
Function FindDataRow(dt As Variant) As Integer
Dim sd2 As Variant
rt = 0
rr = 1
While (Sheet2.Cells(rr, 1).Value <> "") And (rt = 0)
sd2 = Sheet2.Cells(rr, 1).Value
If dt = sd2 Then
rt = rr
Else
rr = rr + 1
End If
Wend
FindDataRow = rt
End Function
Sub FillData(tgv As Variant)
Sheet2.Columns("A:D").Sort Key1:=Sheet2.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Sheet1.Columns("B:D").ClearContents

rr = FindDataRow(tgv)
If rr = 0 Then
MsgBox ("在数据中没有找到 " & tgv)
End
End If

rs = 2
While (Sheet2.Cells(rr, 1) <> "") And _
(Sheet2.Cells(rr, 1).Value = tgv)
Sheet1.Cells(rs, 2).Value = Sheet2.Cells(rr, 2).Value
Sheet1.Cells(rs, 3).Value = Sheet2.Cells(rr, 3).Value
Sheet1.Cells(rs, 4).Value = Sheet2.Cells(rr, 4).Value
rs = rs + 1
rr = rr + 1
Wend
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row() = 2 And Target.Column() = 1 Then
Dim dt As Variant
dt = Sheet1.Range("A1").Value
If dt <> "" Then
Call FillData(dt)
End If
End If
End Sub

热心网友 时间：2024-11-01 13:56

sheet1里A下面的2、3、4是什么啊？怎么与sheet2里面的序号不对应？

热心网友 时间：2024-11-01 13:51

把表格发我邮箱，举例说清楚目的与要求。mfk1288@126.com
1、在这里一填代码会被屏敝而不见我的答复的。
2、因为写代码是要具体那些单元格或范围，有表格容易写与调试。