ksquirt
08-22-2017, 07:05 PM
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
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