Need help with Do While Loop or If this AND that then....

[ksquirt]

Hi All,
I haven't been here for awhile because I haven't been trying to create my own macros, just maintain others! I am now trying to take somebody's recorded macro and add updates. I've done a bit of clean up work, but because of time constraints will need to leave some of that for another day. I think I want a Do While Loop in this instance and I'm having troubles with the AND part of it. Essentially I want to look at column B and while the title is 'Risk Analysis' look in Column D for a 'person's name' and if it's after 3/20/2017 (column M) add 'ORM Approved to Column N. If the 'person's name' does not exist on the Risk Analysis that was created after 3/20/2017 then add 'not ORM Approved' in column N. I can attach the spreadsheet, but am worried about potentially confidential data, so I'd rather not.

This is what I have, at the moment. Please be kind, still a newbie. I do have additional variables as I'm starting to build those for the whole macro.

Sub ORMSearch()
Dim SrchRng As Range, cel As Range
Dim strRA As String
Dim rngB As Range
Dim rngE As Range
Dim rngF As Range
Dim rngM As Range

strRA = "Risk Analysis"
strPP = "Project Plans"
strRE = "R&E Tax Credit Eligibility"
strRT = "Requirements Traceability"
strRM = "Risk Management"
strName = "person's name"
Set rngB = Range("B:B")
Set rngE = Range("E:E")
Set rngN = Range("N:N")
Set rngD = Range("D:D")

  
 Range("B1").Select
 
  Do While ((ActiveCell.Value = "Risk Analysis) >0)
   If InStr(1, cel.Value, "person's name") Then
   cel.Offset(0, 12).Value = "ORM Approved"
  End If
 Loop
  
    
    Range("A2").Select
    
    
End Sub

[Paul_Hossler]

1. I added CODE tags around your macro - you should use the [#] icon to insert [CODE] ..... [/CODE] tags

2. I'd help if you attached a sample workbook with enough data to show what you have to start and where you want to get

[ksquirt]

ORM sample.xlsx
Thanks much for the code tags! I told you, it's been awhile since I've been on here and forgot that part! Sorry! I've attached the sheet with the data. I removed all the names and the macro should search for 'ORM'.

[Paul_Hossler]

There are more elegant ways to do this, but this is the most straight forward when you need to make changes

I think I understand the business rules

Option Explicit

Sub ORM()
    Dim rRow As Range, rData As Range
    Dim rowData As Long
    
    Application.ScreenUpdating = False
    
    Set rData = Worksheets("Approvals").Cells(1, 1).CurrentRegion
    
    For rowData = 2 To rData.Rows.Count
        With rData.Rows(rowData)
            If .Cells(2).Value <> "Risk Analysis" Then GoTo NextRow
            If .Cells(13).Value < DateSerial(2007, 3, 20) Then GoTo NextRow
                        
            .Cells(14).Value = IIf(.Cells(4).Value = "person name", "ORM Approved", "not ORM Approved")
        End With
NextRow:
    Next
    Application.ScreenUpdating = True
End Sub

[ksquirt]

Thanks so much! I see how it's working on your attached file and I changed "person name" as I will need the real name eventually. I then copied the macro to my spreadsheet and ran it and it's putting "ORM Approved" in column A. How do I change this? I was thinking I could add Select("A1") above the statement. It's also putting the data down a row.

[Paul_Hossler]

1. Does your 'real' spreadsheet look like this, starting in row 1 with headers, and Cols B, D, M, and N in the same position?

Capture.JPG

2. If, not then you might have to adjust cells below. It just loops each row from row 2 to the last data row and checks and enters values on that row

Option Explicit
Sub ORM()
    Dim rRow As Range, rData As Range
    Dim rowData As Long
    
    Application.ScreenUpdating = False
    
    Set rData = Worksheets("Approvals").Cells(1, 1).CurrentRegion
    
    For rowData = 2 To rData.Rows.Count
        With rData.Rows(rowData)
            '(2) = column B on 'rowData'
            If .Cells(2).Value <> "Risk Analysis" Then GoTo NextRow
            '(13) = column M on 'rowData'
            If .Cells(13).Value < DateSerial(2007, 3, 20) Then GoTo NextRow
                        
            '(14) = column N on 'rowData', (4) = column D
            .Cells(14).Value = IIf(.Cells(4).Value = "person name", "ORM Approved", "not ORM Approved")
        End With
NextRow:
    Next
    Application.ScreenUpdating = True
End Sub

[ksquirt]

It was missing the header of 'ORM' in column N! It's working now! Thanks SO much!