Search code examples
vbaloopsexcelnested-loops

Loop with multiple constraints

I currently have a macro that looks up to a list (Example below) currently it only pulls back the comments if the question has been failed. See code. what i need to do is build in a simple way to pull back the comments if it is a Pass AND there are comments. if its a pass with no comments it needs to be ignored.

Any ideas I have a few but they seem overly complicated.

Code;

For Each Cell In Sheets("Sharepoint Raw").Range("M2:M3000")
          If Cell.Value = mpan Then
                For Each x In Sheets("Sharepoint Raw").Range("O" & Cell.row & ":AG" & Cell.row & "")
                    If x.Value = "Fail" Then
                        copycount = copycount + 1
                        Sheets(1).Cells(copycount, 2) = Sheets("Sharepoint Raw").Cells(1, x.Column)
                        Sheets(1).Cells(copycount, 4) = "Fail"
                        Sheets(1).Cells(copycount, 5) = Sheets("Sharepoint Raw").Cells(x.row, x.Column + 1)
                        Sheets(1).Cells(305, 16) = Sheets("Sharepoint Raw").Cells(x.row, 37)
                    Else
                        Sheets(1).Cells(305, 16) = Sheets("Sharepoint Raw").Cells(x.row, 37)
                    End If
                Next x
       End If
    Next Cell

Baking table;

╔══════╦══════════════╦══════╦═══════════════╦══════╦═══════════════╦══════╦══════════════════╗
║ Q1.  ║ Q1. Comments ║ Q2.  ║ Q2. Comments? ║ Q3.  ║ Q3. Comments? ║ Q4.  ║  Q4. Comments?   ║
╠══════╬══════════════╬══════╬═══════════════╬══════╬═══════════════╬══════╬══════════════════╣
║ Fail ║              ║ Fail ║               ║ Pass ║               ║ Pass ║                  ║
║ N/A  ║              ║ N/A  ║               ║ Pass ║               ║ Pass ║ Example comment' ║
║ Pass ║              ║ Fail ║               ║ Pass ║               ║ Pass ║                  ║
╚══════╩══════════════╩══════╩═══════════════╩══════╩═══════════════╩══════╩══════════════════╝

need something like (obviously this wont work but is there something similar)

If x.Value = "Pass" AND X+1.value IS NOT NULL Then
Solution

  • Consider the macro below:

    Option Explicit
Sub Offset()

  Dim Cell As Range

  Set Cell = Range("D27")

  Debug.Print Cell.Address
  Debug.Print Cell.Offset(0, 1).Address
  Debug.Print Cell.Offset(5, -3).Address

End Sub

    It outputs the following to the Immediate Window:

    $D$27
$E$27
$A$32

    I have set Cell to an arbitrary cell address. Debug.Print Cell.Address gives the output $D$27 as you would expect.

    Cell.Offset(0, 1) adds 1 to the column number and results in the output $E$27. This is the X+1 you seek.

    The last example is not immediately relevant to your requirement but shows that you can also adjust the row number and that negative offsets are permitted.