Error in the VBA Code

[An Quala]

Hello, can someone please identify what is the error I am facing on the red highlighted part?


PS: Sometimes it also gives error at "Set c = Worksheets(x).UsedRange" but usually it is working fine.

Option Explicit
Sub Xlookup()

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products|Sponsored Brands|Sponsored Display", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
    
    Sheets("Sponsored Products").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Dim lr As Long, lc As Long, i As Long
    Dim a, b
    Dim ws As Worksheet: Set ws = Worksheets("Sponsored Products")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1

    a = Range(ws.Cells(2, 1), ws.Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)

    For i = 1 To UBound(a)
        If a(i, 2) Like "*Campaign*" Or _
        a(i, 2) Like "*Ad Group*" Or _
        a(i, 2) Like "*Bidding Adjustment*" Or _
        a(i, 2) Like "*Product Ad*" Or _
        a(i, 11) Like "*Negative Keyword*" Or _
        a(i, 19) Like "*paused*" Or _
        a(i, 46) Like "*paused*" Or _
        a(i, 47) Like "*paused*" Then b(i, 1) = 1
    Next i
    
      ws.Cells(2, lc).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(lc))

    Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Sort Key1:=ws.Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then ws.Cells(2, lc).Resize(i).EntireRow.Delete
    
Dim lr2 As Long, lc2 As Long, i2 As Long
    Dim a2, b2
Dim ws2 As Worksheet: Set ws2 = Worksheets("Sponsored Brands")
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc2 = ws2.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1

    a2 = Range(ws2.Cells(2, 1), ws2.Cells(lr2, lc2))
    ReDim b2(1 To UBound(a2), 1 To 1)

    For i2 = 1 To UBound(a2)
        If a2(i2, 2) Like "*Campaign*" Or _
        a2(i2, 2) Like "*Draft Campaign*" Or _
        a2(i2, 2) Like "*Draft Keyword*" Or _
        a2(i2, 2) Like "*Draft Negative Product Targeting*" Or _
        a2(i2, 2) Like "*Draft Product Targeting*" Or _
        a2(i2, 2) Like "*Negative Keyword*" Or _
        a2(i2, 13) Like "*paused*" Or _
        a2(i2, 14) Like "*ended*" Or _
        a2(i2, 14) Like "*paused*" Or _
        a2(i2, 14) Like "*rejected*" Or _
        a2(i2, 46) Like "*paused*" Then b2(i2, 1) = 1
    Next i2
    
      ws2.Cells(2, lc2).Resize(UBound(a2)) = b2
    i2 = WorksheetFunction.Sum(ws2.Columns(lc2))

    Range(ws2.Cells(2, 1), ws2.Cells(lr2, lc2)).Sort Key1:=ws2.Cells(2, lc2), order1:=1, Header:=2
    If i2 > 0 Then ws2.Cells(2, lc2).Resize(i2).EntireRow.Delete
    
    Dim lr3 As Long, lc3 As Long, i3 As Long
    Dim a3, b3
Dim ws3 As Worksheet: Set ws3 = Worksheets("Sponsored Display")
    lr3 = ws3.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc3 = ws3.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1

    a3 = Range(ws3.Cells(2, 1), ws3.Cells(lr3, lc3))
    ReDim b3(1 To UBound(a3), 1 To 1)

    For i3 = 1 To UBound(a3)
        If a3(i3, 2) Like "*Campaign*" Or _
        a3(i3, 2) Like "*Ad Group*" Or _
        a3(i3, 2) Like "*Product Ad*" Or _
        a3(i3, 2) Like "*Negative Product Targeting*" Or _
        a3(i3, 13) Like "*paused*" Or _
        a3(i3, 37) Like "*paused*" Or _
        a3(i3, 47) Like "*paused*" Then b3(i3, 1) = 1
    Next i3
    
      ws3.Cells(2, lc3).Resize(UBound(a3)) = b3
    i3 = WorksheetFunction.Sum(ws3.Columns(lc))

    Range(ws3.Cells(2, 1), ws3.Cells(lr3, lc3)).Sort Key1:=ws3.Cells(2, lc3), order1:=1, Header:=2
    If i3 > 0 Then ws3.Cells(2, lc3).Resize(i).EntireRow.Delete


End Sub

 

[rlv01]

Hello, can someone please identify what is the error I am facing on the red highlighted part?


PS: Sometimes it also gives error at "Set c = Worksheets(x).UsedRange" but usually it is working fine.

When asking for help for an error, it helps if you post:

1. Error number
2. Exact error description
3. The line of code where error occurs.

example: "Run-time error 11 - Division by zero"
line of code: Y = X/0

 

[An Quala]

RUNTIME ERROR 9

Subscript Out of Range

@rlv01 please check now.

 

[rlv01]

You did not answer my question #3 above. I'll assume it is this line.

Set c = Worksheets(x).UsedRange

The value of 'x' must be a valid worksheet name, otherwise you will get that error.

 

[An Quala]

You did not answer my question #3 above. I'll assume it is this line.

Set c = Worksheets(x).UsedRange

The value of 'x' must be a valid worksheet name, otherwise you will get that error.

Worksheet name is 100% Valid, but my real problem is highlighted with red please check that one.

 

[An Quala]

You did not answer my question #3 above. I'll assume it is this line.

Set c = Worksheets(x).UsedRange

The value of 'x' must be a valid worksheet name, otherwise you will get that error.

It is Line # 14 from the bottom.

 

[rlv01]

It is Line # 14 from the bottom.

Your code is dynamically sizing array a3 with this line:

    a3 = Range(ws3.Cells(2, 1), ws3.Cells(lr3, lc3))

Probably the error is because the array size of a3 for that particular instance is smaller than you expected and UBound(a3, 2) evaluates to 1 instead of 2. That would produce an error on any line like this one

   a3(i3, 2) Like "*Product Ad*" Or _

that assumes '2' is a valid array index.

For future reference, the better way to indicate the line of code that generates an error is by the use of comments. Example:

    a3 = Range(ws3.Cells(2, 1), ws3.Cells(lr3, lc3))
    ReDim b3(1 To UBound(a3), 1 To 1)

    Dim Condition1 As Boolean

    Condition1 = a3(i3, 2) Like "*Campaign*"                  '<---- runtime error occurred here
    Condition1 = Condition1 Or a3(i3, 2) Like "*Ad Group*"
    Condition1 = Condition1 Or a3(i3, 2) Like "*Product Ad*"
    Condition1 = Condition1 Or a3(i3, 2) Like "*Negative Product Targeting*"
    Condition1 = Condition1 Or a3(i3, 13) Like "*paused*"
    Condition1 = Condition1 Or a3(i3, 37) Like "*paused*"
    Condition1 = Condition1 Or a3(i3, 47) Like "*paused*"

    For i3 = 1 To UBound(a3)
        If Condition1 Then b3(i3, 1) = 1
    Next i3

    ws3.Cells(2, lc3).Resize(UBound(a3)) = b3
    i3 = WorksheetFunction.Sum(ws3.Columns(lc))

 

[An Quala]

Can you please edit the code and solve the error, whatever the error is? @rlv01

 

[rlv01]

Can you please edit the code and solve the error, whatever the error is? @rlv01

Unfortunately no. To solve a problem, one must first determine the cause. Since I cannot the run your code with your data, I can only make educated guesses as to where the problem lies. You must be a participant in the solution. I recommend that you use the VBE editor to single-step through your code while using the watch window to inspect your array variables to find out their values when the error occurs. The error message indicates you are attempting to reference an array element that does not exist. So for example, take this line in your code:

  a3(i3, 47) Like "*paused*"

if array a3 only has 40 columns instead of 47 then you will get a 'subscript out of range' error at that line of code because a3(i3, 47) is not a valid reference. ,