vba 如何在 Excel 中的另一个工作表和使用表单的活动工作表中查找值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11693003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i find a value in another sheet in Excel and in the active sheet using a form?
提问by R. Paez
Well, here is my problem so far, i have a form in VB in this form the user put a number what i want to do is that excel search in Sheet2 If i get this number (if is buyed), and in the active sheet "Data" if is already captured, finally put it in the last empty A row in Sheet1.
嗯，到目前为止，这是我的问题，我在 VB 中有一个表单，这种表单用户输入了一个数字，我想要做的是在 Sheet2 中进行 excel 搜索如果我得到这个数字（如果已购买），并在活动表中“数据”如果已经抓到了，最后放到Sheet1的最后一个空A行。
I have this so far.
到目前为止我有这个。
Private Sub CommandButton1_Click()
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
End Sub
Private Sub CommandButton2_Click()
Dim lastrow As Double
Dim frange As Range
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
If TextBox1.Text = TextBox2.Text Then
Sheets("Sheet2").Activate
ActiveSheet.Range("A2").Select
If Range("A2:A200").Find(What:=TextBox2.Value _
, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate Then
Sheets("Datos").Activate
If Range("A3:A200").Find(What:=TextBox2.Value _
, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate Then
MsgBox ("This number is already registred")
Else
Cells(lastrow + 1, 1) = TextBox2.Value
End If
Else
MsgBox ("The number has not been buyed")
End If
Else
MsgBox ("The number are not the same")
End If
End Sub
I really hope someone you can help me because i am stuck and i do not see the answer.
我真的希望有人可以帮助我，因为我被卡住了，我看不到答案。
Thanks
谢谢
Sorry for my english
对不起我的英语不好
回答by Siddharth Rout
UNTESTED
未经测试
Please see if this is what you are trying?
请看看这是否是您正在尝试的？
Private Sub CommandButton1_Click()
Me.TextBox1.Text = "": Me.TextBox2.Text = ""
End Sub
Private Sub CommandButton2_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim aCell As Range, bCell As Range
Dim lrow As Long
Set ws1 = Sheets("Datos"): Set ws2 = Sheets("Sheet2")
If TextBox1.Text = TextBox2.Text Then
Set aCell = ws2.Columns(1).Find(What:=TextBox2.Value _
, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not aCell Is Nothing Then
Set bCell = ws1.Columns(1).Find(What:=TextBox2.Value _
, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not bCell Is Nothing Then
MsgBox ("This number is already registred")
Else
'~~> This will write to sheet "Datos". if you want to
'~~> write to sheet Sheet2 then change ws1 to ws2 below
With ws1
lrow = ws1.Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(lrow, 1) = TextBox2.Value
End With
End If
Else
MsgBox ("The number has not been buyed")
End If
Else
MsgBox ("The number are not the same")
End If
End Sub
