CODEWITHSUNDEEP
excelvba
Ben Schwabe
Ben Schwabe

Reputation: 1289

Searching for matching cells and copying row data

I'm very new to Visual Basic, so I apologize if this is a basic question.

I have two excel documents, one with a list of company names and their corresponding identifying numbers (about 4000 in total). In a separate document, I have a list of people and who they work for (these are the same companies from the first document, but sometimes there will be more than one row per company, since we work with more than one person from that company). There are about 7000 entries here. I want to transfer all of the company ID numbers into the document with all of the company personnel. I have the concept for the code down, but unfortunately, I don't know enough about Excel VBA syntax to write this. I wrote some code, but it doesnt work and I don't know why.

Sub Firm_Number_Transfer()
    Dim i As Integer
    Dim x As Integer
    Dim row As Integer
    Dim oldRow As Integer
    Dim found As Boolean

    row = 1
    oldRow = 1

    For i = Workbooks("PM Firm Contacts - Step 2 - REVIEWED").Worksheets("Sheet2").Cells("B1") To Workbooks("PM Firm Contacts - Step 2 - REVIEWED").Worksheets("Sheet2").Cells("B7122") Step 1
        row = row + 1
        For x = Workbooks("PM Firms - Step 1 - REVEIWED").Sheets("Sheet1").Cells("B2") To Workbooks("PM Firms - Step 1 - REVEIWED").Sheets("Sheet1").Cells("B4843") Step 1
            oldRow = oldRow + 1
            If i = x Then
                Workbooks("PM Firm Contacts - Step 2 - REVIEWED").Sheets("Sheet2").Cells(row, 1) = Workbooks("PM Firm Contacts - Step 2 - REVIEWED").Sheets("Sheet2").Cells(oldRow, 1)
                found = True
            End If
        Next x
        found = False
        oldRow = 1
    Next i
End Sub

Is there anything obvious that I'm missing? Any help is appreciated.

EDIT

I should also add that the table is set up in such a way that the companies are on the right and the company numbers are on the left. From what I understand, VLOOKUP() only works if the companies are on the left and the company numbers i'm copying are on the right. Otherwise VLOOKUP() would be what I would use.

Upvotes: 0

Views: 3115

Answers (2)

Daniel Möller
Daniel Möller

Reputation: 86600

You can avoid code using a VLOOKUP formula:

In the personel sheet, add in the destination column:

= VLOOKUP(PARAM1, PARAM2, Param3)

Where:

Param1 - The cell in the personel sheet containing company name

Param2 - The range in the company sheet (from colum to column, like A:C), from the column containing company names to the column containind their IDs

Param3 - the index of the column containin the IDs related to the names (if IDs are in column C, for example, and names in Column A, the index is 3 - third column)

You put that formula and drag it down to all personel.

Other option using Index and Match

= index(IDColumn; match(CellWithCompanyNameInPersonelSheet; CompanyNameColumn; 0))

Where you have to use a column, place something like "B:B" for column "B".

Upvotes: 2

rwisch45
rwisch45

Reputation: 3702

This should get you started. Was the statement within the If block a typo? I think you might have meant to copy from one workbook/worksheet to the other (not from the same one)?

Sub Firm_Number_Transfer()
        Dim i As Integer
        Dim x As Integer
        Dim row As Integer
        Dim oldRow As Integer
        Dim found As Boolean
        Dim xlBook As Workbook, xlBook2 As Workbook
        Dim xlSheet As Worksheet, xlSheet2 As Worksheet
        Dim rng As Range, cell As Range, rng2 As Range, cell2 As Range

    xlBook2 = Workbooks("PM Firms - Step 1 - REVEIWED")
    xlSheet2 = xlBook2.Worksheets("Sheet1")
    Set rng2 = xlSheet2.Range("B2:B4843")

    xlBook = Workbooks("PM Firm Contacts - Step 2 - REVIEWED")
    xlSheet = xlBook.Worksheets("Sheet2")
    Set rng = xlSheet.Range("B1:B7122")

    row = 1
    oldRow = 1

    For Each cell In rng
        row = row + 1
        For Each cell2 In rng2
            oldRow = oldRow + 1
            If cell.Value2 = cell2.Value2 Then
                xlSheet.Cells(row, 1) = xlSheet.Cells(oldRow, 1)
                found = True
            End If

        Next
        found = False
        oldRow = 1

    Next

End Sub

Upvotes: 1

Related Questions