vba - Copy from cell if two other cells meet criteria?

Question

any help with this would be appreciated.

I am trying to copy a cell from column (G) into another worksheet, if cells from the same row in column (R) = "Y" and column (B) = "Month"

I had it working for just "Y" criteria but as soon as I added the "Month" this button has stopped working?

this is the code I have;

---

Private Sub CommandButton1_Click()


Dim LR As Long, i As Long
    With Sheets("Savings Q4")
        LR = .Range("R" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With .Range("R" & i)
                If .Value = "Y" Then
            With .Range("B" & i)
                If .Value = "January" Then
                    Sheets("Savings Q4").Range("G:G").Copy Destination:=Sheets("Cifas Loadings").Range("A:A")
              End If
            End With
        Next i
    End With


End Sub

---

Any help would be appreciated.

Thanks

标签： vbainput

Answer 1

At least, you forgot one End With and one End If. But anyway it's not correct with logical part.

Dim LR As Long, i As Long
    With Sheets("Savings Q4")
        LR = .Range("R" & Rows.count).End(xlUp).Row
        For i = 1 To LR
            With .Range("R" & i)
                If .Value = "Y" Then
                    With .Range("B" & i)
                        If .Value = "January" Then
                            Sheets("Savings Q4").Range("G:G").Copy Destination:=Sheets("Cifas Loadings").Range("A:A")
                        End If
                    End With
                End If
            End With
        Next i
    End With

It's easier to make it w/o With, like

if Range("R"& i).value="Y" and Range("B" & i).value = "Month"
     COPE CELLS
end if