0

I am trying to loop across each cell in a range (IN ACTIVE TAB "Import Data") and match it with values from range in de-actvie Tab ("BU Names"), in case of a cell that holds a string that is not compliant with my selected range i would like to inform the user about an error

WHEN IT WORKS? When instead of few cells within a range I have only one.

ERRORS WHILE: Range is brighter.

working part

With Worksheets("BU Names")

    LastBU = .Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("BU Names").Activate
    .Range(.Cells(2, 1), .Cells(LastBU, 1)).Select
    Worksheets("Import Data").Activate

End With

help below please

Myrange = Range("E5:G6")

For Each cel In Myrange       
    Set cel_checker = Selection.Find(What:=cel, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=True, SearchFormat:=False)        

    If cel_checker Is Nothing Then
        MsgBox "Please correct " & cel2 & " as this is not valid Business Unit"
    End If

Next

The problem seems to be at "Set cel_checker = Selection.Find" when it keeps under cel_cheker 'nothing' instead of values which is correct

Community
  • 1
  • 1
Dejw
  • 1
  • 2
  • 1
    Do try to avoid using [.Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). That may be your problem. Instead, try `Set cel_checker = Worksheets("BU Names").Find` – PartyHatPanda Sep 21 '16 at 13:48
  • Thanks for your answer. Although, it's not working due to bad reference here. We cannot refer to whole worksheet i believe, at least I am getting here "Run-time error '438' Object doesn't support this property or method" – Dejw Sep 21 '16 at 14:06
  • Oh my mistake, I forgot to add the range. How about try something like `Set cel_checker = Sheets("BU Names").Range(Sheets("BU Names").Cells(2, 1), Sheets("BU Names").Cells(LastBU, 1)).Find`, and note you may need to move the `LastBU` variable outside of the `With`, unsure of how VBA uses variable scope regarding `With` statements. – PartyHatPanda Sep 21 '16 at 14:57
  • I all you need to do is check to see if a value is in a `Range`, you can skip the `.Find` and use `WorksheetFunction.CountIf(searchRange, value) <> 0` instead. – Comintern Sep 21 '16 at 16:13
  • thanks guys, Tim was right ;) – Dejw Sep 22 '16 at 08:28

1 Answers1

0

This should do what you want:

Set Myrange = Worksheets("Import Data").Range("E5:G6") '<<<< need Set here

Myrange.Parent.Activate

For Each cel In Myrange

    Is IsError(Application.Match(cel.Value, _
               Worksheets("BU Names").Range("A:A"), 0)) Then

          MsgBox "Please correct " & cel.Address & " ('" & cel.Value & _
                 "') as this is not valid Business Unit"
    End If

Next
Tim Williams
  • 154,628
  • 8
  • 97
  • 125