时间戳

首页 > 解决方案 > 通过比较 Excel VBA 中两个不同工作表的 ID 来获取数据时出现问题。给出不正确的数据

excel - 通过比较 Excel VBA 中两个不同工作表的 ID 来获取数据时出现问题。给出不正确的数据

问题描述

截屏获取第一周的薪水我已经在 excel 中编写了 VBA 代码，以在单个主付款表中获取司机每周数据。我使用驱动程序 ID 具有主键来获取驱动程序数据。共有 4 周报告 MCMSSummaryReport(Week1)、MCMSSummaryReport(Week2)、MCMSSummaryReport(Week3)、MCMSSummaryReport(Week4)。

我正在尝试通过比较驱动程序 ID 来获取工作表“Monthly Payment Master2”中的数据。“月付Master2”有司机ID列表。我将 Monthly Payment Master2 的驱动程序 ID 与其他 4 个每周报告进行比较。

但是，当代码在 Monthly Payment Master2 表中的每周报告中找不到相同的 ID 时，它应该在“Week1”列中返回“”（空白）。它返回 Ids 不匹配的空白，但之后循环跳过一行并从 1+1 行获取数据。

无法在代码中解决此问题。

下面是 excel 宏启用表链接：https ://drive.google.com/open?id=1aaidUeED7rkXaw-rMHoMK-4TNzkUJlN4

下面是代码：

    Private Sub CommandButton1_Click()

Dim salary As String, fromdate As String
Dim lastcoluns As Long, lastrow As Long, erow As Long, ecol As Long, lastrow1 As Long
lastcoluns = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Sheet7.Cells(Rows.Count, 1).End(xlUp).Row + 1

MsgBox (lastrow1)


Dim i As Integer
i = 2
Do While i < lastrow1
    temp1 = Worksheets("Monthly Payment Master2").Cells(i, 1)
    For j = 2 To lastrow + 1
        temp2 = Worksheets("MCMSSummaryReport(week 1)").Cells(j, 1)
        If temp1 = temp2 Then
            salary = Sheet1.Cells(i, 18).Value
            Worksheets("Monthly Payment Master2").Cells(i, 7) = salary

        Else

        End If
    Next j
    i = i + 1
Loop
MsgBox ("Week-1 data submitted successfully, Please submit Week-2 Data.")
Application.CutCopyMode = False
Sheet6.Columns().AutoFit
Range("A1").Select

End Sub

标签： excelvbacomparison

解决方案

我建议更改循环的架构，使其更易于阅读和更健壮：

Dim salary As String
Dim wsMaster As Worksheet, wsReport As Worksheet
Set wsMaster = ThisWorkbook.Worksheets("Monthly Payment Master2")
Set wsReport = ThisWorkbook.Worksheets("MCMSSummaryReport(week 1)")

lastrow1 = wsMaster.Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = wsReport.Cells(Rows.Count, 1).End(xlUp).Row

Dim i As Long
Dim foundRange As Range

Dim temp1 As String

For i = 2 To lastrow
    temp1 = wsMaster.Cells(i, 1).Value2

    Set foundRange = wsReport.Range("A2:A" & lastrow2).Find(temp1, LookAt:=xlWhole, MatchCase:=True)

    If foundRange Is Nothing Then
        salary = vbNullString
    Else
        salary = foundRange.Offset(0, 17).Value2
    End If
    wsMaster.Cells(i, 7) = salary
Next i

请注意，您没有使用lastcoluns、fromdate和。此外，您应该始终如一地参考您的工作表，使用或，但不要将两者用于同一个工作表，因为这会使其他读者感到困惑。ecolerowSheet1Worksheets("Name")

推荐阅读