PDA

View Full Version : [SOLVED] Need help with Do While Loop or If this AND that then....



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

Paul_Hossler
08-22-2017, 07:26 PM
1. I added CODE tags around your macro - you should use the [#] icon to insert
..... 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
08-22-2017, 07:38 PM
20147
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
08-23-2017, 06:40 AM
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
08-23-2017, 07:09 AM
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
08-23-2017, 08:42 AM
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?

20155

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
08-23-2017, 09:56 AM
It was missing the header of 'ORM' in column N! It's working now! Thanks SO much!