代码之家 › 专栏 › 技术社区 › A Cohen

VBA-在两张纸内查找ID并在单独的工作簿中过帐

excel-vba vba excel

A Cohen · 技术社区 · 6 年前

我使用了一个双循环,在相应的表中搜索匹配的id。所有变量都已正确定义,但由于某些原因,只有4个名称复制到单独的工作簿中,我知道还有更多。每张纸上都有1000多个数字,我不确定是否与此有关。

我的代码背后的逻辑是,第一个循环保持放置状态,直到它在另一个工作表中找到匹配的数字,然后如果它找到了,它将退出第二个循环并在第一个循环中开始下一个数字,然后在第二个循环中重新开始。

代码:

Dim tw As ThisWorkbook: Set tw = ThisWorkbook
Dim s1 As Excel.Worksheet: Set s1 = tw.Worksheets(2)
Dim rwCnt1 As Integer: rwCnt1 = s1.Range("A1", s1.Range("A1").End(xlDown)).Rows.Count
Dim nav As Excel.Worksheet: Set nav = tw.Worksheets("Navigator")
Dim rwCnt2 As Integer: rwCnt2 = nav.Range("A1", nav.Range("A1").End(xlDown)).Rows.Count
Dim x As Integer, y As Integer, z As Integer

Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim tempWB As Workbook
Dim tempWS As Worksheet
Dim i As Integer

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.InitialFileName = "Libraries\Documents"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

FileChosen = fd.Show
If FileChosen = -1 Then

    s1.Columns(16).NumberFormat = "@"
    s1.Range("A3:Z" & rwCnt1).Sort key1:=s1.Range("P1"), order1:=xlAscending, Header:=xlYes

    For i = 1 To fd.SelectedItems.Count

        Set tempWB = Workbooks.Open(fd.SelectedItems(i))
        Set tempWS = tempWB.Worksheets(1)
        tempWS.Columns(4).NumberFormat = "@"

        tempWB.Unprotect

        z = 1

        For y = 4 To rwCnt1
            For x = 3 To rwCnt2
                If nav.Cells(x, 5).Value2 = s1.Cells(y, 16).Value2 Then
                    z = z + 1
                    With nav
                        tempWS.Cells(z, 1) = .Cells(x, 2).Value2
                        tempWS.Cells(z, 2) = .Cells(x, 3).Value2
                        tempWS.Cells(z, 3) = .Cells(x, 4).Value2
                        tempWS.Cells(z, 4) = .Cells(x, 5).Value2
                        tempWS.Cells(z, 7) = .Cells(x, 6).Value2
                        tempWS.Cells(z, 10) = .Cells(x, 7).Value2
                        tempWS.Cells(z, 11) = .Cells(x, 8).Value2
                        tempWS.Cells(z, 12) = .Cells(x, 9).Value2
                    End With
                    Exit For
                End If 
            Next x
        Next y

    Next i
End If

我只是不明白为什么只有4个人转学过来。如果有人比我有更好的解决方案,请告诉我。我试图使用 If Not "Variable" Is Nothing Then ,但我不知道如何应用它。提前谢谢!

1 回复 | 直到 6 年前

A Cohen 6 年前

问题是数字格式无法正确读取对方。

因此,我添加了两个短行来更改数字格式。从我的原始代码中调用它们。

代码:

Private Sub reformat_nav_col5()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Navigator")
Dim c As Range

ws.Activate
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
For Each c In Selection
    c = c * 1  ' convert "number stored as text" to number
Next c
Selection.NumberFormat = "000000000" '"@"

Range("E3").Select

End Sub
'----------------------------------------------------
Sub reformat_visa_col16()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Visa")
Dim c As Range

ws.Activate
Range("P4").Select
Range(Selection, Selection.End(xlDown)).Select
For Each c In Selection
    c = c * 1  ' convert "number stored as text" to number
Next c
Selection.NumberFormat = "000000000" '"@"

Range("P4").Select

End Sub