CODEWITHSUNDEEP
excelvbaloops
Rhyfelwr
Rhyfelwr

Reputation: 329

Wrote a VBA script that makes sense but still doesn't work

Hi so I am trying to run the following script but all it does is put "N/A" in a column and just loop through all the other cells below without doing anything.

Sub NA()
    Range("E1").Select
    Do Until IsEmpty(ActiveCell)
        If Range("E1") = "password_update" Then If Range("G1") = Range("J1") Then Range("B1") = "N/A"
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

What I'm trying to do is check if the cell "E1" has the value "password_update" in it and if yes then also check if cell "G1" and cell "J1" has matching content, and if all these criteria matches then type "N/A" to cell "B1".

I also wanted to have this script loop through all subsequent rows (E1 then E2 then E3 and so on you get the idea). Now I am no VBA expert, but I wrote this script and no matter how many times I look it makes sense to me logically but it still doesn't work. Any ideas? Help would be appreciated.

Upvotes: 1

Views: 54

Answers (2)

QHarr
QHarr

Reputation: 84465

Similar to code above but if you want to be more explicit about what is happening and the sheets you are using (in case you run when in the wrong sheet for example consider putting something similar to below). This uses the same logic as you used

Sub UpdateColBwithNA() 'Place in a standard module
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim currentRow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1") 'Change to name of sheet you are running code against

    With ws

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("E1").Activate

        For currentRow = 1 To LastRow
            If .Cells(currentRow, "E") = "password_update" And _
               (.Cells(currentRow, "G") = .Cells(currentRow, "J")) Then
                    .Cells(currentRow, "B") = "N/A"
            End If
        Next currentRow
        End With
End Sub

The With statement means you don't have to go to the sheet.

Upvotes: 0

Kresimir L.
Kresimir L.

Reputation: 2441

Try this. Whenever possible, avoid using select statement in your code.

Sub NA()
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
For X = 1 To lastrow
    If Range("E" & X).Value = "password_update" And Range("G" & X) = Range("J" & X) Then Range("B" & X) = "N/A"
Next X
End Sub

Upvotes: 0

Related Questions