lundi 25 juin 2018

VBA For Loop Where Each Answer must = True

I have a VBA macro I'm working on that has several loops running.

Where I'm stuck, however, is that I want the macro to go through all m's and if all are True, then move on to the next set of code. If any one is false, then I want it to go to the next For loop within which this is embedded.

         For m = 2 To 29
            If Worksheets("Current Content Analysis").Cells(contentrownum, contentcolnum) = False And _
            (Worksheets("Keyword Categorization").Cells(keywordrownum, m) = Worksheets("Product Categorization").Cells(productrownum, (m + 1)) Or _
            Worksheets("Keyword Categorization").Cells(keywordrownum, m) = "All") Then Next m

Above is the specific piece of code that I want to identify whether, for each m, the conditions mentioned are False. If any are False, then it should move on to the next j in the outer loop.

Below is the full loop code if needed.

Full Code:

'outer loops through each ASIN (i)
 For Each i In Worksheets("Background Search Term Analysis").Range("B5", Worksheets("Background Search Term Analysis").Cells(LastRow, 2)).Cells
    contentrownum = Application.Match(i, Worksheets("Current Content Analysis").Range("B1", Worksheets("Current Content Analysis").Cells(LastRow, 2)).Cells, 0)
    productrownum = Application.Match(i, Worksheets("Product Categorization").Range("A1", Worksheets("Product Categorization").Cells(LastRow, 1)).Cells, 0)

    'inner loops through each keyword (j)
    For Each j In Worksheets("Current Content Analysis").Range("M2", Worksheets("Current Content Analysis").Cells(2, LastColumn)).Cells
        contentcolnum = Application.Match(j, Worksheets("Current Content Analysis").Range("A2", Worksheets("Current Content Analysis").Cells(2, LastColumn)).Cells, 0)
        keywordrownum = Application.Match(j, Worksheets("Keyword Categorization").Range("A1", Worksheets("Keyword Categorization").Cells(LastKeywordRow, 1)).Cells, 0)

        'prints the current values for each variable as the loop progresses
        'end values should match printed last row and column; helps identify where breaks occur
        Worksheets("Current Content Analysis").Cells(1, 4).Value = contentrownum
        Worksheets("Current Content Analysis").Cells(1, 5).Value = productrownum
        Worksheets("Current Content Analysis").Cells(1, 6).Value = contentcolnum
        Worksheets("Current Content Analysis").Cells(1, 7).Value = keywordrownum

       'if this product doesn't currently have the keyword (j) in it and the keyword tags match the product tags then
       For m = 2 To 29
        If Worksheets("Current Content Analysis").Cells(contentrownum, contentcolnum) = False And _
        (Worksheets("Keyword Categorization").Cells(keywordrownum, m) = Worksheets("Product Categorization").Cells(productrownum, (m + 1)) Or _
        Worksheets("Keyword Categorization").Cells(keywordrownum, m) = "All") Then Next m 

        'if all of m loop are True, then move on to this step; if any m is False, then move on to next j
        WrdArray() = Split(j)
            For k = LBound(WrdArray) To UBound(WrdArray)
                If InStr(LCase(result_no_dup), LCase(WrdArray(k))) = 0 _
                And InStr(LCase(Worksheets("Current Content Analysis").Cells(contentrownum, 12).Value), LCase(WrdArray(k))) = 0 Then
                    result_no_dup_compact = result_no_dup_compact & WrdArray(k)
                    If Len(result_no_dup_compact) > Worksheets("Instructions").Range("B4").Value Then Exit For
                    result_no_dup = result_no_dup & " " & WrdArray(k)
                End If
            Next k
            result_no_spaces = result & j
            If Len(result_no_spaces) > Worksheets("Instructions").Range("B4").Value Then Exit For
            'if true and character limit not exceeded, add the keyword (j) to our result concatentation
            result = result & " " & j
            'if true and character limit not exceeded, add the keyword (j) to our results concatenation with commas
            result_commas = result_commas & ", " & j
        End If
    Next j

   'once i go through all of my keywords, set ASIN background search term cell value equal to result
   Worksheets("Background Search Term Analysis").Cells(contentrownum, 4).Value = Right(result, Len(result) - 1)
   Worksheets("Background Search Term Analysis").Cells(contentrownum, 5).Value = Right(result_commas, Len(result_commas) - 2)
   Worksheets("Background Search Term Analysis").Cells(contentrownum, 6).Value = Right(result_no_dup, Len(result_no_dup) - 1)
   'reset results to empty for next ASIN (i)
   result = ""
   result_no_spaces = ""
   result_commas = ""
   result_no_dup = ""
   result_no_dup_compact = ""
Next i

Thank you for the help!

Aucun commentaire:

Enregistrer un commentaire