none
Change Cell Value Based on Another Cell Value RRS feed

  • 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?

    • Edited by Nerdy Brd Tuesday, July 3, 2012 6:34 PM
    Tuesday, July 3, 2012 6:33 PM

All replies

  • 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

    Tuesday, July 3, 2012 6:46 PM
  • I made the suggested changes, but still nothing happened. Thanks for your time.
    Tuesday, July 3, 2012 6:59 PM
  • 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

    Tuesday, July 3, 2012 7:09 PM