如何在不激活表单上的单元格时引用它?

So I'm running an audit on a set of digital files but I think the way I wrote is pretty convoluted so maybe someone can help me...

所以我正在对一组数字文件进行审计，但是我认为我写的方式非常复杂，也许有人能帮我……

What I can't figure out is why activesheet.cells is working fine, but when I try to reference the cells with Workbooks("Master List").worksheets("Master Shipped").cells it gives me an error. I've tried it with the full file location, I've tried it with worksheets.cells, I've tried it with sheets.cells, I've tried making the worksheet a variable, and I can't get it to work.

我搞不懂为什么要主动。单元格可以正常工作，但是当我尝试使用工作簿引用单元格时(“主列表”)。工作表(“主运”)。单元格它会给我一个错误。我尝试了完整的文件位置，我尝试了工作表。细胞，我用床单试过了。单元格，我试过把工作表变成一个变量，但是我不能让它工作。

It does work at the top where I'm naming the variables, but it doesn't work in my if statements which is where I want it work because I don't want to have to activate the sheet 10 times each loop.

它在我命名变量的顶部是有效的，但是它在if语句中不起作用，这是我想要的因为我不想每次循环都要激活10次表。

Application.ScreenUpdating = False
LR = Cells(Rows.Count, 2).End(xlUp).Row
Dim AuditSheet As Worksheet, Mastersheet As Worksheet
Set AuditSheet = ThisWorkbook.Sheets("Audit")
Set Mastersheet = ThisWorkbook.Sheets("Master Shipped")

For r = 3 To LR
   FpathBOL = "U:\Warehouse Associate\Shipped Orders 2016\Bill of Lading\"
   fnamebol = Mastersheet.Cells(r, 21).Text
   FNamePOD = Left(Mastersheet.Cells(r, 21).Text, (Len(Mastersheet.Cells(r, 21)) - 8))
   FpathFile = "V:\LVA Files\" & Mastersheet.Cells(r, 4).Value & "\Line " & Mastersheet.Cells(r, 10).Value & "\"
   FnameFile = Mastersheet.Cells(r, 4).Value & "-"
   BOL = FpathBOL & "\" & fnamebol & ".pdf"
   POD = FpathBOL & FNamePOD & "POD.pdf"
   File1 = FpathFile & FnameFile & "PO.pdf"
   File2 = FpathFile & FnameFile & "EIC PO.pdf"
   File3 = FpathFile & FnameFile & "EDI 855.pdf"
   File4 = FpathFile & FnameFile & "EDI 870.pdf"
   File5 = FpathFile & FnameFile & "VENDOR INVOICE.pdf"
   File6 = FpathFile & FnameFile & "EIC INVOICE.pdf"
   File7 = FpathFile & FnameFile & "PCGR.pdf"
   File8 = FpathFile & FnameFile & "PL.pdf"
   File9 = FpathFile & FnameFile & "EDI.pdf"
If Dir(File1) = "" Then
    Workbooks("Master List.xlsm").Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File1
End If
If Dir(File2) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File2
End If

If Dir(File3) = "" And Dir(File9) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File3
End If
If Dir(File4) = "" And Dir(File9) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File4
End If
If Dir(File5) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File5
End If
If Dir(File6) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File6
End If
If Dir(File7) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File7
End If
If Dir(File8) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File8
End If
If Dir(BOL) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = FnameFile & BOL
End If
If Dir(POD) = "" Then
    Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = FnameFile & POD
End If
Next r
Application.ScreenUpdating = True
End Sub

1 个解决方案

#1

To take your existing code snippet:

获取现有代码片段:

If Dir(File1) = "" Then
    Workbooks("Master List.xlsm").Worksheets("Audit").Activate
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Activate
    ActiveCell.Value = File1
End If

As a rule of thumb, you can remove the trailing .Activate and the leading ActiveSheet/ActiveCell statements, and merge the lines:

根据经验，您可以删除后面的.Activate和前面的ActiveSheet/ActiveCell语句，并合并行:

If Dir(File1) = "" Then
    Workbooks("Master List.xlsm").Worksheets("Audit").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = File1
End If

But, it's only a rule of thumb. Notice for example, the use of Rows.Count. That is relative to the active sheet, so it won't work without further editing.

但是，这只是一个经验法则。注意，例如，使用Rows.Count。这是相对于活动页，所以如果没有进一步的编辑，它将无法工作。

If Dir(File1) = "" Then
    With Workbooks("Master List.xlsm").Worksheets("Audit")
      .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = File1
    End With
End If

#1