if statement - updating cells based on condition

[kiwikiki718]

hello have a range of cells that I would like to be updated if the if and statement is true.

ex. if cell P23 is True and if cell ranges M25 is not blank, I want the code to clear the data in the column O25 and update the text to Working. I Would like this code to run through all cells within the range M25:M75.

here is the code that I have where I am receiving an error


If Range("P23") = True And Range("M25:M75") <> "" Then
Range("O25:O75").ClearContents
Range("O25:O75").Value = "Working"
End If
End Sub

 

[Skyybot]

So you want to go through each cell of Range M25:M72 and if that cell is not empty AND Cell P23 is True, then cell in Column O in the same row should have the Value "Working"? Is Cell P23 Boolean or Text?

 

[kiwikiki718]

So you want to go through each cell of Range M25:M72 and if that cell is not empty AND Cell P23 is True, then cell in Column O in the same row should have the Value "Working"? Is Cell P23 Boolean or Text?

Correct. Cell P23 is text.

 

[Skyybot]

Try this. And remember to test on a copy of your Workbook in case there are unexpected results. Please change "YourSheetName" to the name of the sheet your are using and place this Macro in the Workbook, not a Module.

Sub verify()
Dim wb As Workbook, sht As Worksheet, srchRng As Range, cell As Range, chkCell As Range
Dim i As Long
Set wb = ThisWorkbook: Set sht = wb.Worksheets("YourSheetName"): Set srchRng = sht.Range("M25:M72")
Set chkCell = sht.Range("P23")
If chkCell.Value = "True" Then
    For Each cell In srchRng
        If Not cell.Value = "" Then
            cell.Offset(0, 2).Value = "Working"
        End If
    Next cell
End If
End Sub

 

[kiwikiki718]

Try this. And remember to test on a copy of your Workbook in case there are unexpected results. Please change "YourSheetName" to the name of the sheet your are using and place this Macro in the Workbook, not a Module.

Sub verify()
Dim wb As Workbook, sht As Worksheet, srchRng As Range, cell As Range, chkCell As Range
Dim i As Long
Set wb = ThisWorkbook: Set sht = wb.Worksheets("YourSheetName"): Set srchRng = sht.Range("M25:M72")
Set chkCell = sht.Range("P23")
If chkCell.Value = "True" Then
    For Each cell In srchRng
        If Not cell.Value = "" Then
            cell.Offset(0, 2).Value = "Working"
        End If
    Next cell
End If
End Sub

Thank you this worked! Instead of referencing a Specific worksheet name, how do I reference the active work sheet instead.

 

[Skyybot]

Sub verify()
Dim wb As Workbook, sht As Worksheet, srchRng As Range, cell As Range, chkCell As Range
Dim i As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet: Set srchRng = sht.Range("M25:M72")
Set chkCell = sht.Range("P23")
If chkCell.Value = "True" Then
    For Each cell In srchRng
        If Not cell.Value = "" Then
            cell.Offset(0, 2).Value = "Working"
        End If
    Next cell
End If
End Sub

 

[kiwikiki718]

Sub verify()
Dim wb As Workbook, sht As Worksheet, srchRng As Range, cell As Range, chkCell As Range
Dim i As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet: Set srchRng = sht.Range("M25:M72")
Set chkCell = sht.Range("P23")
If chkCell.Value = "True" Then
    For Each cell In srchRng
        If Not cell.Value = "" Then
            cell.Offset(0, 2).Value = "Working"
        End If
    Next cell
End If
End Sub

Thanks alot. This worked.