if statement - updating cells based on condition

kiwikiki718

Board Regular
Joined
Apr 7, 2017
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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.
 
Upvote 0
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.
VBA Code:
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
 
Upvote 1
Solution
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.
VBA Code:
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.
 
Upvote 0
VBA Code:
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
 
Upvote 1
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top