find duplicate and clear possible corresponding matching value

[SarahPintal]

Hi guys,

I cant seem to work out how to reference the drang (named range) and the cell propertly as i keep getting an error.

Everything in the below code works except the parts below in blue

details:
I have in column X lots of names and in column Y (dynamic column\ranges as the users chooses them as per the code) and I want to loop through all names in 'X' and if there are multiple of the same names then for the corresponding 'Y' row entry of each be checked and if they are equal (same $ value) then for one of them of cells in the Y column to be cleared.

Basically if the same name appears more than once it will most likley have the same $ value and if i do a calculation on the $'s it will be 2,3,10 times the amount it should be, hence the need for this .

As per the below code I am trying to use 2 for Each loops though I keep getting an error when trying to reference the drng range which has the values in them..

Private Sub CommandButton1_Click()
  Dim DataSH As Worksheet, rng As Range, iRange As Range, oRange As Range
  Dim drng As Range
  Set DataSH = Sheets("Intergral Dump")
  coloff = ComboBox1.ListIndex + 1
  UserForm1.Hide
  Set iRange = Application.InputBox(Prompt:= _
                "Please Select the First cell of the INPUT range (WBS list) with your mouse.", _
                    Title:="Specify INPUT Range Cell", Type:=8)
  
  Set oRange = Application.InputBox(Prompt:= _
                "Please Select the First cell of the OUTPUT range (where you want the values to be appear) with your mouse.", _
                    Title:="Specify OUTPUT Range Cell", Type:=8)
  
  
  With Sheets(iRange.Parent.Name)
    Set rng = .Range(iRange, .Cells(Rows.Count, iRange.Column).End(xlUp))
    rowoff = 0
    For Each ce In rng
      Set FindIT = DataSH.Range("A:A").Find(what:=ce.Value, lookat:=xlPart)
      If Not FindIT Is Nothing Then
        oRange.Offset(rowoff, 0).Value = FindIT.Offset(0, coloff).Value
      Else
        oRange.Offset(rowoff, 0).ClearContents 'clear out any previous data
      End If
      rowoff = rowoff + 1
    Next ce
  
  
  End With

'***********code issue below**********'
With Sheets(oRange.Parent.Name) 'using the users selected ranges WS.
 Set drng = .Range(oRange, .Cells(Rows.Count, oRange.Column).End(xlUp)) 'as the user selected 1 cell this will use the entire used column as the range.
 For Each mainCell In rng
    For Each testCell In rng
        If (mainCell.Value = testCell.Value) And (drng.mainCell.Value = drng.testCell.Value) Then
 
            testCell.Value = ""
 
        End If
    Next testCell
Next mainCell
'***********code issue above**********'
End With
  Unload Me
  
End Sub

Private Sub UserForm_Initialize()
  ComboBox1.List = Array("July", "August", "September", "October", "November", "December", "January", "February", "March", "April", "May", "June")
  ComboBox1.ListIndex = 0
End Sub