[已解决]求助VBA查找引用,谢谢

时间

32N7qoeR.rar (67.21 KB, 下载次数: 70)

2010-8-15 18:00 上传

点击文件名下载附件
求助VBA查找引用,谢谢
根据查询条件,将"回迁二期入住明细"表里的相关数据引入到"入户结算通知单模板"表里相应的黄色单元格内.命令按钮在"入户结算通知单模板"右上方.急!!!!!!!!!!!!

最佳答案

月排行榜 / 总排行榜

uranus1997

2010-8-15 20:36

看你的代码就累，给你整好看点

Private Sub CommandButton1_Click()

    Dim xyid As String
    Dim hang As Integer
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    xyid = InputBox("需要查询的ID", "查询回迁户协议号")
    Set sh1 = ThisWorkbook.Sheets("入户结算通知单模板")
    Set sh2 = ThisWorkbook.Sheets("回迁二期入住明细")
    sh2.Activate

    For hang = 2 To 1000
        If sh2.Cells(hang, 1).Value = xyid Then
            MsgBox ("协议号" & sh2.Cells(hang, 1) & Chr(13) & "姓名" & sh2.Cells(hang, 2))
            s = sh1.Range("A1:A2")
            sh1.Range("f3") = sh2.Cells(hang, 2)
            sh1.Range("r3") = sh2.Cells(hang, 3)
            sh1.Range("AO3") = sh2.Cells(hang, 4)
            Exit For
        End If
    Next hang

    If hang = 1001 Then MsgBox ("未查到该回迁户")
    sh1.Activate
End Sub

跳转到最佳答案楼层

时间

有金币噢,先到先得.

时间

来人啊,快给答案啊,再不给答案就要出人命啦

时间

Private Sub CommandButton1_Click()
Sheets("回迁二期入住明细").Select
Dim xyid As String
Dim hang As Integer
Dim tempmsgbox As VbMsgBoxResult
xyid = InputBox("需要查询的ID", "查询回迁户协议号")
For hang = 1 To 1000
  If Sheets("回迁二期入住明细").Cells(1, hang) = xyid Then  (应用程序或定义对象错误)
     tempmsgbox = MsgBox("协议号" & Cells(1, hang) & Chr(13) & "姓名" & Cells(2, hang))
     Workbooks("入户结算单(新)").Sheets("入户结算通知单模板").Range("f3") = Workbooks("入户结算单(新)").Sheets("回迁二期入住明细").Cells(2, hang)
     Workbooks("入户结算单(新)").Sheets("入户结算通知单模板").Range("r3") = Workbooks("入户结算单(新)").Sheets("回迁二期入住明细").Cells(3, hang)
     Workbooks("入户结算单(新)").Sheets("入户结算通知单模板").Range("AO3") = Workbooks("入户结算单(新)").Sheets("回迁二期入住明细").Cells(4, hang)
     Exit For
  End If
Next hang
 tempmsgbox = MsgBox("未查到该回迁户")
End Sub

uranus1997

If Sheets("回迁二期入住明细").Cells(hang, 1) = xyid Then 

时间

 tempmsgbox = MsgBox("协议号" & Cells(1, hang) & Chr(13) & "姓名" & Cells(2, hang))(只显示了"协议号"字段与"姓名"字段,具体内容没有)
     Workbooks("入户结算单(新)").Sheets("入户结算通知单模板").Range("f3") = Workbooks("入户结算单(新)").Sheets("回迁二期入住明细").Cells(hang, 2)(这行提示下标越界)

时间

调试好啦,谢谢

uranus1997

看你的代码就累，给你整好看点

Private Sub CommandButton1_Click()

    Dim xyid As String
    Dim hang As Integer
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    xyid = InputBox("需要查询的ID", "查询回迁户协议号")
    Set sh1 = ThisWorkbook.Sheets("入户结算通知单模板")
    Set sh2 = ThisWorkbook.Sheets("回迁二期入住明细")
    sh2.Activate

    For hang = 2 To 1000
        If sh2.Cells(hang, 1).Value = xyid Then
            MsgBox ("协议号" & sh2.Cells(hang, 1) & Chr(13) & "姓名" & sh2.Cells(hang, 2))
            s = sh1.Range("A1:A2")
            sh1.Range("f3") = sh2.Cells(hang, 2)
            sh1.Range("r3") = sh2.Cells(hang, 3)
            sh1.Range("AO3") = sh2.Cells(hang, 4)
            Exit For
        End If
    Next hang

    If hang = 1001 Then MsgBox ("未查到该回迁户")
    sh1.Activate
End Sub

时间

切.

时间

QUOTE:

以下是引用uranus1997在2010-8-15 20:36:00的发言：

看你的代码就累，给你整好看点

Private Sub CommandButton1_Click()

    Dim xyid As String
    Dim hang As Integer
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    xyid = InputBox("需要查询的ID", "查询回迁户协议号")
    Set sh1 = ThisWorkbook.Sheets("入户结算通知单模板")
    Set sh2 = ThisWorkbook.Sheets("回迁二期入住明细")
    sh2.Activate

    For hang = 2 To 1000
        If sh2.Cells(hang, 1).Value = xyid Then
            MsgBox ("协议号" & sh2.Cells(hang, 1) & Chr(13) & "姓名" & sh2.Cells(hang, 2))
            s = sh1.Range("A1:A2")
            sh1.Range("f3") = sh2.Cells(hang, 2)
            sh1.Range("r3") = sh2.Cells(hang, 3)
            sh1.Range("AO3") = sh2.Cells(hang, 4)
            Exit For
        End If
    Next hang

    If hang = 1001 Then MsgBox ("未查到该回迁户")
    sh1.Activate
End Sub

s = sh1.Range("A1:A2")

上边这个等式是什么意思呀.看不明白,好像没用上吧