Change Cell Value Based on Another Cell Value

Question

Sub X()
    
    Dim rngData As Range
    Dim lngRow As Long
    With Sheets("SECURITY REPORT") 'spreadsheet with data
    Set rngData = Range("A1", Cells(Rows.Count, 1).End(xlUp))
    
    
    End With
    For lngRow = 1 To rngData.Rows.Count
       
            
                If UCase(rngData.Cells(lngRow, 1).Offset(0, 8).Value) = "NA" Then '''want to see if "NA" is in column J
                    rngData.Cells(lngRow, 1).Offset(0, 9).Value = "TRUE" '''If "NA" is in Column J, want to change column K to "TRUE"
                End If
         
        
    Next
            
End Sub


Found this code on another thread and have tried to change it to suit my needs, but it doesn't work at all. Can someone give me some guidance?

Answer 1

Although the line

Set rngData = Range("A1", Cells(Rows.Count, 1).End(xlUp))

is between With Sheets("SECURITY REPORT") and End With, it doesn't refer to the Security Report sheet at all. To do so, change the line to

Set rngData = .Range(.Range("A1"), .Cells(.Rows.Count, 1).End(xlUp))

---

Regards, Hans Vogelaar

Answer 2

I made the suggested changes, but still nothing happened. Thanks for your time.

Answer 3

Your offsets are 1 off: to refer to a cell in column J, use

rngData.Cells(lngRow, 1).Offset(0, 9)

or somewhat shorter

rngData.Cells(lngRow, 10)

For column K, use

rngData.Cells(lngRow, 1).Offset(0, 10)

or

rngData.Cells(lngRow, 11)

---

Regards, Hans Vogelaar