Compare & add if not found routine 1

[Walter349]

I need an excel macro that will check the value of a cell in the column of one sheet, with the values of a column in a 2nd sheet. If the value is not found on the 2nd sheet, then that value will be added to the column of the 2nd sheet and an associated formula already present on the 2nd sheet, copied to an adjacent cell of the new value.

Any thoughts ???

 

[EBGreen]

It may not be the best solution, but I would read the values of column 1 into a dictionary as the keys then for each value in column 2 I would use the .Exists method to see if the value is already in column 1.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Nikita6003]

Walter349

Try using the Find method: this returns a range object if the value yoou search for is found.

e.g. searching for the contents of Sheet1 Cell A1 on Sheet2:

Dim l_wksFindValue As WorkSheet
Dim l_wksSearchIn As WorkSheet
Dim l_rngRangeFound As Range

'Set the worksheet objects
Set l_wksSearchIn = ThisWorkbook.WorkSheets(2)
Set l_wksFindValue = ThisWorkbook.WorkSheets(1)

'This assumes you'll be looping through a column of values
For l_lRow = 1 To l_wksFindValue.Columns(1).Rows.Count
    Set l_rngRangeFound = l_wksSearchIn.UsedRange.Find(l_wksFindValue.Cells(l_lRow,1))
    'test whether the search returns a Range
    'if it doesn't, then the vakue needs adding to the second worksheet
    If l_rngRangeFound Is Nothing Then
        'Add value to second sheet
        'Copy formula
    End If
Next l_lRow

'Reset objects
Set l_wksFindValue = Nothing
Set l_wksSearchIn = Nothing
Set l_rngRangeFound = Nothing

HTH

Cheers
Nikki
Look, mommy, I'm flying!