1个工作簿中的VBA检查条件，另一工作簿中的输入值 [英] VBA check criteria in 1 workbook, input values in another workbook

问题描述

我正在努力寻找如何使代码达到我想要的目的，我用网上可以找到的东西尽我所能地编写了代码，但是在某些方面，我不知道该写什么.

I am struggling to figure out how to get the code to do what I want, I wrote as much as I could with what I could find online, but for some aspects, I don't know what write.

代码的目的

在"spreadsheet 2017"中检查每个名称旁边的数字"1".如果为"1"，则在另一个名为日期模板"的工作簿的一行中的多个单元格中输入值(与"spreadsheet 2017"中的名称在同一行)

Check "spreadsheet 2017" for the number "1" next to each name. If there is a "1", then enter values in multiple cells in a row in another workbook called "Dates template" (in the same row as name in "spreadsheet 2017")

我想要做什么的详细信息

1. A和B列具有列表的名字(A)和姓氏(B)
2. 在C列中的名称旁边检查"1"
3. 如果有1，则切换到工作簿日期模板"
4. 在A列中找到相同的名称，并分别输入值7.2(列B)，3.9(列C)和74.2(列D).

这是代码的基本骨干，将来我将寻求添加其他条件，例如D列中的1和C列中的1.因此，case函数可能会更好地工作，然后它将来会更容易添加.我不知道哪个更好.

This is the basic backbone of the code, in the future I will be looking to add additional conditions, such as 1 in column D for example as well 1 in column C. so maybe a case function would work better, then it would be easier to add in the future. I don't know which is better.

在下面的代码中，我只写了它来扫描B列中的姓氏，因为没有重复项.但将来，可能会有相同的姓氏，在这种情况下，也必须阅读姓氏.这是我对如何同时检查两者感到困惑的地方.

In the code below I only wrote it to scan through the surnames in column B, because there are no duplicates. but in the future, it is likely that there will be names with the same surname, in which case first name will have to be read as well. This is where I'm confused on how to check both.

如果可以将名称列表复制到日期模板"，然后在符合条件的情况下输入这些值，则可以作为奖励.因为此刻我手动将名称输入到日期模板"电子表格中.

A bonus would be if its possible to copy the list of names over to the "Dates template" and then input those values if the criteria is met. Because at the moment I manually type up the names onto the "Date Template" Spreadsheet.

Sub Summary()

    Dim wb1 As Workbook
    Dim Sht As Worksheet
    Dim Rng As Range
    Dim wb2 As Workbook
    Dim cell As Range
    Dim ws As Worksheet

    Set wb1 = Workbooks("Works template.xlsm")
    Set wb2 = Workbooks("Spreadsheet 2017")
    Set Sht = wb1.Worksheets("Template") 
    Set ws = wb2.Worksheets("January")
    Set Rng = ws.Range("B7:B" & Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row)

    For Each cell In Rng
        If cell.Offset(0, 2).Value = "1" Then
            Sht.Cells.Offset(0, 3).Value = "7.2" '<--- This is where I get the new error, "Application-defined or object-defined error"
            Sht.Cells.Offset(0, 2).Value = "3.9"
            Sht.Cells.Offset(0, 6).Value = "74.2"
        End If
    Next
End Sub

非常感谢！

推荐答案

Sht.Cells 引用工作表中的所有单元格，因此您不能使用 .Offset().

Sht.Cells refers to all cells in the sheet, so you can't use .Offset() on this.

Sub Summary()

    Dim wb1 As Workbook
    Dim Sht As Worksheet
    Dim Rng As Range
    Dim wb2 As Workbook
    Dim cell As Range
    Dim ws As Worksheet

    Set wb1 = Workbooks("Works template.xlsm")
    Set wb2 = Workbooks("Spreadsheet 2017")
    Set Sht = wb1.Worksheets("Template") 
    Set ws = wb2.Worksheets("January")
    Set Rng = ws.Range("B7:B" & Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row)

    For Each cell In Rng
        If cell.Offset(0, 1).Value = "1" Then
            sht.Range(cell.address).Offset(-2, 0).Offset(0, 0).Value = "7.2"
            sht.Range(cell.address).Offset(-2, 0).Offset(0, 1).Value = "3.9"
            sht.Range(cell.address).Offset(-2, 0).Offset(0, 2).Value = "74.2"
        End If
    Next
End Sub

这篇关于1个工作簿中的VBA检查条件，另一工作簿中的输入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！