CODEWITHSUNDEEP
vbaexcel
Prajakta
Prajakta

Reputation: 11

Compare a column in 2 excel files in VBA

I have 2 excel sheets with almost same information.

1st sheet looks like : 

ID Name empnumber
1  abc  123
2  xyz  234

2nd sheet looks like : 

ID Name empnumber
1  abc  234
2  xyz  345

My 1st sheet contains temporary Employee number and 2nd sheet contains final employee number.

I am trying to create a macro which will update my first sheet-empnumber column depending upon my second sheet by compairing Name or ID value.

I will take 1st and 2nd excel files as user input. also comparing column (Name/Id) in inputbox so user will decide what to match.

Pseudo-code:

if abc from 1st sheet==abc from 2nd sheet
then 
 emp number of abc in 1st sheet = empnumber of abc from 2nd sheet.

How do I do this in VBA?

Here goes my code:

Dim FileNameA As String
Dim FileNameB As String
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long

strRangeToCheck = "A1:B5"

iRet = MsgBox("Select forecast file", vbYes, "Forecast file")
FileNameA = Application.GetOpenFilename()
If FileNameA = "False" Then Exit Sub
Set wbkA = Workbooks.Open(Filename:=FileNameA)
Set sh1 = wbkA.Sheets("Sheet1")
varSheetA = wbkA.Worksheets("Sheet1").Range(strRangeToCheck)

iRet = MsgBox("Select Actual file", vbYes, "Actual file")
FileNameB = Application.GetOpenFilename()
If FileNameB = "False" Then Exit Sub
Set wbkB = Workbooks.Open(Filename:=FileNameB)
Set sh2 = wbkB.Sheets("Sheet1")
varSheetB = wbkB.Worksheets("Sheet1").Range(strRangeToCheck)

strcompareColumn = InputBox("Please enter column name to compare", "Data Entry")
strUpdateColumn = InputBox("Please enter column name to update", "Data Entry")

End Sub

Now I need to find exact row/column from 1st sheet, take value from second sheet and update 1st sheet.

Upvotes: 0

Views: 1084

Answers (1)

CustomX
CustomX

Reputation: 10113

Here's an option without using a macro, only works if your files are static. You have your 2 files and you open them in the same Excel instance (easiest way to reference them). In your main file you add this formula to one of your cells.

=IF(ISERROR(VLOOKUP(A2;[Book2]Sheet1!$A$2:$C$4;3;0));IF(ISERROR(VLOOKUP(B2;[Book2]Sheet1!$B$2:$C$4;2;0));"";VLOOKUP(B2;[Book2]Sheet1!$B$2:$C$4;2;0));VLOOKUP(A2;[Book2]Sheet1!$A$2:$C$4;3;0))

Upvotes: 1

Related Questions