1

I want to verify that data in cells of a particular column does not have typos (and/or are in a pre-approved list).

When I tried expanding the range of values to check for 26 or more I get an error. I learned that VBA imposes a limit of 25 lines on certain elements of macros. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-too-long

A sample of my code shortened to show what I am trying to achieve. In the real use case, the code is far longer.

Cities that are valid are flagged in green. Anything else is flagged in red.

Sub checkCities()

    Dim myRange As Range
    Dim myCell As Range
    Set myRange = Range("A2:A1000")
    For Each myCell In myRange
    If myCell Like "New York" Or _
      myCell Like "Chicago" Or _
      myCell Like "Boston" Then

    myCell.Interior.Color = RGB(0, 128, 0)

    Else: myCell.Interior.Color = RGB(128, 128, 0)

    End If
    Next myCell

End Sub

Are there any workarounds?

Community
  • 1
  • 1
William Lombard
  • 337
  • 1
  • 3
  • 14
  • 1
    *"limit of 25 lines on certain elements of macros."* - No, it's a max of 25 lines joined by line continuation characters. Just don't put a single city per line or use a suitable data structure like an array of city names and use a loop to check if `myCell` is `Like` any elements in that array. – Nick is tired Aug 17 '21 at 08:55
  • Thanks for making that clear. I am still a noob to VBA. Could you write a short example of how I should make the code work with arrrays? Thanks. – William Lombard Aug 17 '21 at 08:57
  • https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba – Nick is tired Aug 17 '21 at 08:57
  • 1
    You could instead use data validation or a look-up list in a hidden resource worksheet that you can check against. – Tragamor Aug 17 '21 at 09:06
  • I'll give that a try as well. Thanks guys, I have made some progress with this. – William Lombard Aug 17 '21 at 09:11
  • 1
    You'd find this easier to mange using a range on a sheet to hold the list - then you can do a (simpler) `Match()` to check each value instead of having the whole list embedded in your code. – Tim Williams Aug 17 '21 at 16:07
  • Thanks for the tip. In the end, I used Nick's suggestion of adding the values to an array and then checking if the value of each cell was found in the array (or not). – William Lombard Aug 17 '21 at 17:44

1 Answers1

1

Here's a way to do it using a Select Case instead of an If/Else

Select Case True
    Case myCell Like "New York"
        myCell.Interior.Color = RGB(0, 128, 0)
    Case myCell Like "Chicago"
        myCell.Interior.Color = RGB(0, 128, 0)
    Case Else
        myCell.Interior.Color = RGB(128, 128, 0)
End Select

You should be able to get more than 25 of them then.

You can also combine them like this:

Select Case True
    Case myCell Like "New York", myCell Like "Chicago"
        myCell.Interior.Color = RGB(0, 128, 0)
    Case Else
        myCell.Interior.Color = RGB(128, 128, 0)
End Select

I'm not a big fan of line continuations anyway.

braX
  • 11,506
  • 5
  • 20
  • 33
  • 1
    Excellent! The second of the two suggestions seems like to easiest to digest and thus far (on a small set of 'approved' values) it is working! I"ll test it and see if I can overcome the 25 item 'limit'. :-) – William Lombard Aug 17 '21 at 09:09
  • It can work with a long list of values but not to the degree I was hoping. I'm using the macro to verify the spelling of almost 1000 employees (and not cities). It throws an error, 'procedure too large' but I got around it by adding them to an array instead and then checking if the value each cell in present in the array. It works a beauty. I would still use your solution for shorter lists. – William Lombard Aug 17 '21 at 17:42