StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIdentifying duplicate values across two worksheets
text
Body
copied!<p>I have two worksheets, one which contains historical data and one which contains data I have just imported. I would like to check the existing data to see if the new data contains any duplicate quotes (which would therefore have been "converted" into orders).</p> <p>Currently I am taking the quote number in A2 and comparing it to the quote numbers in the other worksheet using the following code:</p> <pre><code>Dim wb As Workbook Set wb = ActiveWorkbook Dim wS As Worksheet Set wS = ActiveSheet Dim importWS as worksheet importWS = sheets.("NEWDATA") select.importWS Range("A1").Select Do ActiveCell.Offset(1, 0).Select 'Set the current quote number as a value to be located Dim valueToFind As Long valueToFind = ActiveCell.Value Dim checkRange As Range Set checkRange = wS.Range("D1:D" & EntryRow) 'Check the existing worksheet to see if the quote already exists Dim xlCell As Range For Each xlCell In checkRange If xlCell.Value = valueToFind Then problems: Dim existQuote As range existQuote = xlCell.Address Dim existingRow As Integer existingRow = existingQuote.Row MsgBox valueToFind & "in row" & existingRow & "has been converted to an order" End If Next xlCell Loop Until ActiveCell.Value > 300000 Or ActiveCell.Value = "" ActiveCell.Offset(-1, 0).Select Dim DataPoints As Integer DataPoints = ActiveCell.Row ActiveCell.Offset(1, 0).Select </code></pre> <p>I am having problems with the if function at "problems:" The code successfully identifies that quote numbers which are duplicated, but I would like it to be check if another cell in the same row = "sales order" and then perform an action based on this.</p> <p>I cannot think of a way to get the row number of the cell I am in using this code, so that I can check it and edit the entry.</p> <p><strong>EDIT: The <code>.address</code> function returns a <code>string</code>, not a <code>range</code>. This was why it was failing.</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload