在另一个工作表中查找单元格 [英] Finding Cell in another sheet

问题描述

我为申请付款计划的学生创建了一个数据库.我有3张纸:

I created a database for students who have applied for payment plan. I have 3 sheets:

查询"-这是在等待学生注册时存储数据的地方.

"Enquiry" - this is where the data is stored while waiting for the students to sign up.

已确认"-该数据具有3行标题:学生ID(B1)，计划ID(B2)和付款ID(B3).在学生注册后，我将在此处输入其他数据.

"Confirmed" - this data has 3 row headings: Student ID (B1), Plan ID (B2) and Pay ID (B3).This is where I will input the additional data after the student signs up.

主列表"-确认付款计划的学生的最终列表.

"Masterlist" - the final list of students who confirmed the payment plan.

我的想法是，在已确认"表中手动输入数据，然后运行一个宏，在查询"表中搜索学生ID(B1)的值.然后，我希望它在查询"表的B和E列中复制计划ID(B2)和付款ID(B3).

The idea is for me to manually input the data in the "Confirmed" sheet then run a macro that searches the value of the Student ID (B1) in the "Enquiry" sheet. I then want it to copy the Plan ID (B2) and Pay ID (B3) in column B and E on the "Enquiry" sheet.

填充数据后，我需要将整行转移到主列表"工作表中.

Once the data is populated, I need the whole row transferred to the "Masterlist" sheet.

我尝试记录Marco，但是当我记录它时，它发现它只是该单元格的当前值.我对公式相当满意，但之前从未真正做过宏.

I've tried recording Marco but what it finds it only the current value of the cell when I recorded it. I'm fairly good with formula but haven't really done macros before.

Range("B1").Select
Selection.Copy
Sheets("Enquiry").Select
Cells.Find(What:="4536092", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Rows("29:29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Masterlist").Select
ActiveWindow.LargeScroll Down:=-7
Rows("2:2").Select
Sheets("Enquiry").Select
Range("A29").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "4536092"
Rows("29:29").Select
Selection.Copy
Sheets("Masterlist").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown

推荐答案

不是所有的vba优点都可以通过宏记录器访问.下面的代码可在我根据图纸布局描述构建的测试工作簿中使用.将来，请添加屏幕截图或更好的工作簿的保管箱xlsx.还要注意，这里很少有人会花时间从头开始构建代码.

Not all of vba goodness is accessible via the macro recorder. Code below works in the test workbook I built based your sheet layout description. In the future, please add screenshots or better yet a dropbox xlsx of your workbook. Also note that very few people here will take the time to build your code from scratch.

我对此发表了很多评论.玩这个.评论有问题，我会回答.

I've commented this out quite a bit. Play with this. Comment with questions and I will answer.

Sub macroName()

        'declaring range object Ranges hold values and cell addresses at the same time
    Dim id As Range
    Dim found As Range

        'setting id var
    Set id = Worksheets("Confirmed").Range("b2")

        'making sure id we are checking isn't null
    If Not id.Value = "" Then
            'finding the value for var id.  xlWhole means exact cell match.
        Set found = Worksheets("Enquiry").Cells.find(What:=id, LookAt:=xlWhole)
        MsgBox "Inserted new row in Masterlist for ID: " & id
    Else
        MsgBox "ID cannot be null at: " & id.Address
    End If

        'making sure we had a match from .find
    If Not found Is Nothing Then
            'inserting a new row
        Worksheets("Masterlist").Range("a2").EntireRow.Insert
            'setting new row to value of entire row value of range object.
        Worksheets("Masterlist").Range("a2").Value = found.Rows.Value
    Else
        MsgBox "Nothing found for id: " & id.Value
    End If
End Sub

这篇关于在另一个工作表中查找单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！