0

I am new to writing in excel and coding in general. I wrote this to check if a corresponding cell was blank and if it wasn't, to loop through the array. If any of the array is present in the cell, then the target cell value will be "Y".

It gets through one row then stalls. I isolated the issue to be the for loop but I cannot figure out what is causing it to crash Excel. Any help is greatly appreciated.

Sub test()
    Dim LR As Long, i As Long, j As Long, aNames
    aNames = Array("this", "that", "the other")
    
    Cells(2, 21).Activate

    Do While Not IsEmpty(ActiveCell.Offset(, -15))
        For j = LBound(aNames) To UBound(aNames)
            On Error Resume Next
            If ActiveCell.Offset(, -15).Value Like "*" & aNames(j) & "*" Then
                ActiveCell.Value = "Y"
                ActiveCell.Offset(1, 0).Activate
                On Error GoTo Last
            Else
            End If
        Next j
    Loop
Last:
End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
Sean Sullivan
  • 3
  • 4
  • When you say crashes, do you mean the Excel process terminates, stops responding to input, or stops running this macro? Unrelated to that, have you considered [a formula](https://stackoverflow.com/a/14804422/11683)? – GSerg Feb 03 '23 at 01:26
  • Crashes as in "Not Responding" white screen. I have to force close the application. I just tried removing the do while loop and the code ran that one row without crashing. I have a feeling it is the order in which I am asking for these loops. When you say formula, do you mean in VBA? I am more comfortable using formula but not when looping through an array. This is the first time I am attempting this outside of a tutorial. – Sean Sullivan Feb 03 '23 at 01:37
  • No, I mean a formula, like the one that can be accessed by clicking the link above. And in order to stop VBA code that keeps doing something it shouldn't you can press Ctrl+Break and then debug said code with F8. – GSerg Feb 03 '23 at 01:39
  • 1
    As for why your loop does not end, consider what happens when the current row does not contain any of your sought for words. – GSerg Feb 03 '23 at 01:43
  • Haha. I see now. Sorry about that. I will test this now. Note: I got "Run-time error (Method 'Value' of object 'Range' failed)" on my last test. – Sean Sullivan Feb 03 '23 at 01:44
  • Hi GSerg. This worked well! I will try and keep things more simple like in the example you provided. Thanks for all of your help! – Sean Sullivan Feb 03 '23 at 02:26

0 Answers0