VBA "If then" statement to find specific values in a column, if true a value will be changed in another column

Question

I am trying to create a button in Excel using VBA code. The button will run VBA code to find all cells in column-D with a "Y" AND all cells in column-E with a "Y". If this statement is true, all corresponding cells in column-J will be replaced with a "Y". I have tried the following with no success:

Sub Simple_If() 
    If Range("D:D").Value = "Y" And Range("E:E").Value = "N" Then
        Range("J:J").Value = "Y"
    End If 
End Sub

I have now tried the following code:

Dim x As Integer 
With Sheets("Sheet2")
LastRow = .Range("D" & .Rows.Count).End(xlUp).Row
For x = 2 To LastRow
    If Cells(x, 4).Value = "Y" And Cells(x, 5).Value = "N" Then
    Cells(x, 10).Value = "Y"
    
Else
    Cells(x, 10).Value = "N"

End If
Next x
End Sub

The new code does not edit the sheet based upon the conditions called, but I do not get errors? What is the problem here? Thank you for any help or guidance.

@BigBen: I tried the following loop with no success: 'Sub If_else_using_For() Dim x As Integer Dim nRows As Double 'First count nRows with data, the last Row nRows = ThisWorkbook.Application.WorksheetFunction.CountA(Range("D:D")) For x = nRows To nRows If Cells(x, 4).Value = "Y" And Cells(x, 5).Value = "N" Then Cells(x, 10).Value = "Y" Else Cells(x, 10).Value = "N" End If Next x End Sub' I want the loop to count the rows of data automatically as the Excel sheet is not static, more data is added daily. — Josh B, Apr 29 '21 at 16:34
See [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the proper way to find the last row. Also you need `For x = 1 to nRows`, or maybe `For x = 2 to nRows` if you don't want to consider row 1. — BigBen, Apr 29 '21 at 16:35
@BigBen: do I need code to find the last row for what I'm trying to accomplish? It seems as though 'nRows' will allow the number of rows in data to be included in the "If Then" statement, correct?? I did take your suggestion to modify my code. New code used is (code is still not working, but no errors): 'Sub If_else_using_For() Dim x As Integer Dim nRows As Double nRows = ThisWorkbook.Application.WorksheetFunction.CountA(Range("D:D")) For x = 2 To nRows If Cells(x, 4).Value = "Y" And Cells(x, 5).Value = "N" Then Cells(x, 10).Value = "Y" Else Cells(x, 10).Value = "N" End If Next x End Sub' — Josh B, Apr 29 '21 at 16:52
`CountA` counts the number of non-blank cells. Its result will only correspond to the last row if your data is contiguous and begins in row 1, which is why it's unreliable. — BigBen, Apr 29 '21 at 16:53
@BigBen: Please see my edited post above with the new code I used after reading the link you shared. The new code still not produce edits to the sheet that are being called? — Josh B, Apr 29 '21 at 19:28
Add a `.` in front of all of the `Cells` calls so that they refer to `With Sheets("Sheet2")`: `.Cells(x, 4).Value`, `.Cells(x, 5).Value ` and so on. — BigBen, Apr 29 '21 at 20:03
@BigBen: After adding the "." in front of "Cells" it made an "End With" expected error pop-up. I fixed that. Now I get a subscript out of range error? — Josh B, Apr 29 '21 at 23:30
That means the `ActiveWorkbook` has no sheet named "Sheet2". — BigBen, Apr 29 '21 at 23:38
@BigBen ah hah! fixed it. Now it works like a charm. Hats off to you BigBen. Thank you for the help. — Josh B, Apr 29 '21 at 23:45

score 1 · Answer 1 · edited Apr 30 '21 at 02:54

Thanks to @BigBen I was able to fix my code. See below for the working code.

Sub If_else_using_For()
    Dim x As Long, LastRow As Long
    With Sheets("Sheet name")
        LastRow = .Range("D" & .Rows.Count).End(xlUp).Row

        For x = 2 To LastRow
            If .Cells(x,4).Value = "Y" And .Cells(x,5).Value = "N" Then
                .Cells(x,10).Value = "Y"
            Else
                .Cells(x,10).Value = "N"
            End If
        Next x
    End With
End Sub

score 0 · Answer 2 · answered Apr 28 '21 at 23:43

I assumed there are no cells with blank spaces

Assign this macroto your button

I hope help you... good Luck

Sub CustomSub()

    'nRows will count rows with data
    'the formula compares if D and E are equal to "Y"
    'if you  dont use headers, replace "J2" with "J1" in code

    Dim nRows As Double
    'First count nRows with data, the last Row
    nRows = ThisWorkbook.Application.WorksheetFunction.CountA(Range("D:D"))
    ' we will write the formula in first cell in column J, it should be J2 if you use headers or J1 if dont
    Range("J2").Select
    'This formula compare both columns vs Y
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-5]=""Y"",RC[-6]=""Y""),""Y"",""N"")"
    'Finally we will copy this formula in all other cell using the nRows value
    ActiveCell.Copy
    Range("J2:J" & nRows).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    'if you need just  go to the first row
    Range("J1").Select
End Sub

Some cells have blanks... sorry I should have disclosed that. I assume that alters the suggested code? — Josh B, Apr 29 '21 at 00:31
I tried the following loop with no success: 'Sub If_else_using_For() Dim x As Integer Dim nRows As Double 'First count nRows with data, the last Row nRows = ThisWorkbook.Application.WorksheetFunction.CountA(Range("D:D")) For x = nRows To nRows If Cells(x, 4).Value = "Y" And Cells(x, 5).Value = "N" Then Cells(x, 10).Value = "Y" Else Cells(x, 10).Value = "N" End If Next x End Sub' I want the loop to count the rows of data automatically as the Excel sheet is not static, more data is added daily. — Josh B, Apr 29 '21 at 16:40

VBA "If then" statement to find specific values in a column, if true a value will be changed in another column

2 Answers2