CODEWITHSUNDEEP
vbaexcel
midn8
midn8

Reputation: 13

Excel/VBA Comparing two columns and copying the cell contents from a third column into a fourth column in two different sheets

I have to write a macro in Excel for a co-worker. It should import .xlsx files and then compare first and last names between two columns in two different sheets. If a match has been found in the second sheet, the cell contents of another column in the second sheet have to be copied and pasted into a column in the first sheet. The macro to import the files is done and works perfectly. Due to me being very new to Macros/VBA, I'm experiencing some difficulties in comparing between the sheets and copying.
Example:

Sheet 1

Column A (First and Last Name)

Column N (DEPARTEMENTS)

Sheet 2

Column B (DEPARTEMENTS)

Column C (First and Last Name)

As you can see, the user "John Doe" is present in Column A in sheet 1 AND in Column C in sheet 2. Now I need to copy the cell contents of Column B (for the same person) to the Column N in the same row as "John Doe". How can I do this?

This is what I have so far:

Sub compareCols()
    Dim masterWorkbook As Workbook
    Set masterWorkbook = Application.ActiveWorkbook

    Dim masterSheet_caption As String
    Dim masterSheet_msg As String
    Dim masterSheet_Name As String
    masterSheet_caption = "Choosing a mastersheet"
    masterSheet_msg = "In which sheet should the departement of the users be written in if a match has been found in the imported sheet? (this will be the mastersheet)"
    masterSheet_Name = InputBox(masterSheet_msg, masterSheet_caption, User)

    Dim masterSheet_columnFL_msg As String
    Dim masterSheet_columnFL_FirstCell As String
    masterSheet_columnFL_msg = "Which is the first cell (in the mastersheet) in which the first and the last names of the users can be found?"
    masterSheet_columnFL_FirstCell = InputBox(masterSheet_columnFL_msg, masterSheet_caption, User)

    Dim importedSheet_caption As String
    Dim importedSheet_msg As String
    Dim importedSheet_Name As String
    importedSheet_caption = "Choosing the imported sheet"
    importedSheet_msg = "What sheet has been imported to check for duplicates in the columns containing first and last names? (default: import)"
    importedSheet_Name = InputBox(importedSheet_msg, importedSheet_caption, User)

    Dim masterSheet As Worksheet
    Dim importedSheet As Worksheet
    Set masterSheet = masterWorkbook.Worksheets(masterSheet_Name)
    Set importedSheet = masterWorkbook.Worksheets(importedSheet_Name)

End Sub

FOLLOWUP

At least VBA is English only. Therefore I'm going to try that. This should do the trick:

Sub test()
Dim masterWorkbook As Workbook
Set masterWorkbook = Application.ActiveWorkbook

Dim masterSheet As Worksheet
Dim importedSheet As Worksheet
Set masterSheet = masterWorkbook.Worksheets("Online")
Set importedSheet = masterWorkbook.Worksheets("import")

masterSheet.Range("N1:N871").Formula = _
"=INDIRECT(""importedSheet!B"" & INDEX(MATCH(A1,importedSheet!C:C,0),0,0))"

masterSheet.Range("N1:N871").Value = masterSheet.Range("N1:N871").Value
End Sub

All I get with this is 0s in the column N in sheet 1 (masterSheet). But at least it is something! Now I'll just need to find the "error". And I guess it's the traduction. Give me 5 minutes.

Upvotes: 1

Views: 2089

Answers (2)

midn8
midn8

Reputation: 13

At least VBA is English only. Therefore I'm going to try that. This should do the trick:

Sub test()
Dim masterWorkbook As Workbook
Set masterWorkbook = Application.ActiveWorkbook

Dim masterSheet As Worksheet
Dim importedSheet As Worksheet
Set masterSheet = masterWorkbook.Worksheets("Online")
Set importedSheet = masterWorkbook.Worksheets("import")

masterSheet.Range("N1:N871").Formula = _
"=INDIRECT(""importedSheet!B"" & INDEX(MATCH(A1,importedSheet!C:C,0),0,0))"

masterSheet.Range("N1:N871").Value = masterSheet.Range("N1:N871").Value
End Sub

All I get with this is 0s in the column N in sheet 1 (masterSheet). But at least it is something! Now I'll just need to find the "error". And I guess it's the traduction. Give me 5 minutes.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

NON VBA SOLUTION

A Simple formula like

=INDIRECT("Sheet2!B" & INDEX(MATCH(A1,Sheet2!C:C,0),0,0))

Can solve your problem

enter image description here

VBA SOLUTION

I have hardcoded the cell address to show an example. Please amend as applicable.

Sheet1.Range("N1:N3").Formula = _
"=INDIRECT(""Sheet2!B"" & INDEX(MATCH(A1,Sheet2!C:C,0),0,0))"

Sheet1.Range("N1:N3").Value = Sheet1.Range("N1:N3").Value

Upvotes: 2

Related Questions