Compare and Match 2 Columns and Copy the values of Matched items from next Column in Workbook 1 to Empty Column in Workbook 2 against Matched items
First of all really sorry for disturbing you people. I am new to VBA Excel and have no previous experience. I am trying a thing from last 2 weeks and still I am stuck at some point. I have written a small code that does not completely fulfill what I need to do.
If anybody help me out in this regard then I shall be really grateful to you.
I have written this program for 2 separate sheets but I have originally 2 separate workbooks and I want code to be written for 2 separate workbooks.
I have 2 separate Workbooks. In Workbooks 1, Sheet name (AM_quote-overview_sales-inputs) I have 2 columns. Column A contains Topic Information and In Column B I have the data related to the information.
In Workbook 2 I have Column A containing the Topic Information words some are similar to what I have in AM_quote-overview_sales-inputs Sheet and some are not and in Column B I I need values to be copied from Column B of Workbook 1 sheet (AM_quote-overview_sales-inputs) on matching.
I want a macro in Workbook 2 (Sheet 1) that compares the values of Topic Information present in Column A with Topic Information Present in Column A of Workbook 1 Sheet (AM_quote-overview_sales-inputs) and then copies the values from Column B of workbook 1 sheet (AM_quote-overview_sales-inputs) to Column B of workbook 2 (Sheet 1) .
My written code compares the words but when I add new row in Sheet 1 of Workbook 2 the values that are copied from Column B of Workbook 1 to workbook 2 Column B are not accurate.
I need to compare 2 columns and copies the values of Column B of Workbook 1 Sheet (AM_quote-overview_sales-inputs) to Column B of Workbook 2 (Sheet1) for the compared or matched words from Column A of both sheets.
Please have a look at the figures below for detailed information. I hope that i remained able to explain my query in words. I shall be grateful to you if somebody help me out. Thanks in anticipation.
Private Sub CommandButton1_Click() Dim oldRow As Integer Dim newRow As Integer Dim i As Integer i = 1 For oldRow = 1 To 1170 For newRow = 1 To 1170 If StrComp((Worksheets("AM_quote-overview_sales-inputs").Cells(oldRow, 1).Text), (Worksheets("Sheet1").Cells(newRow, 1).Text), vbTextCompare) <> 0 Then i = oldRow Worksheets("Sheet1").Cells(i, 2) = " " Else Worksheets("Sheet1").Cells(i, 2) = Worksheets("AM_quote-overview_sales-inputs").Cells(newRow, 2) i = i + 1 Exit For End If Next newRow Next oldRow End Sub
1:[WorkBook 1 Sheet (AM_quote-overview_sales-inputs) Data]
2:[Workbook 2 (Sheet 1) Data ]
Workbook 1 Sheet AQR Data WorkBook 2 Sheet 1 Col A Col B Col A Col B Ford 3 BMW BMW 4 Ford Jaguar 5 Rolls Royce Rolls Royce 6 Jaguar
I have 2 Columns in workbooks.
I need a macro in Workbook 2 Sheet 1 that will pic up the values likes BMW etc from Column A and match these values present in Column A of WorkBook 1 Sheet AQR and the words which gets matched it copies the values of words like 3, 4 from Column B of Workbook1 to Column B of Work book 2 infront of Words.
Like Infront of BMW I need Value like 4 so after matching words I need 4 in Col B of workbook 2.
- If no value is matched or new row is added in Workbook2 which do not contain some word or value so it should be left empty and I need the values of matched words to be copied in front of respective words.