Did you mean to post this in this thread?

In any case, watch your unqualified ranges. They're going to cause trouble.

Mike H wrote:

Hi,

You can narrow the range down considerably with this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim MyRange as Range
Dim c As Range
For Each wS In Worksheets
If wS.Range("D1").Value = "changed" Then
lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row
lastrow1 = Cells(Cells.Rows.Count, "BL").End(xlUp).Row
Set myrange = Range("X1:X" & WorksheetFunction.Max(lastrow, lastrow1))
For Each c In myrange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
ThisWorkbook.Save
End Sub

Mike

"akemeny" wrote:

That worked perfectly. Thank you!!

"Dave Peterson" wrote:

First, if you're going to use that With/End with structure, you'll want to make
sure you prefix properties with a dot.

This code
With Worksheets("Oct 8 - 2054543")
For Each c In Range("y6:y137")

Will look thru Y6:Y137 of the active sheet (if the code is in a general module).

You'd want to use:
With Worksheets("Oct 8 - 2054543")
For Each c In .Range("y6:y137")
(notice that dot in front of Range(). That means it belongs to the object in
the previous with statement.

This is untested, but it did compile:

Option Explicit
Sub Sonic()
Dim iRow As Long
Dim wks As Worksheet
Dim myStr As String

Set wks = Worksheets("Oct 8 - 2054543")

With wks
For iRow = 6 To 137
myStr = ""
If .Cells(iRow, "B").Value = "" Then
Select Case UCase(.Cells(iRow, "Y").Value)
Case Is = "A"
myStr = "No medical necessity for IP status; " _
& "should have been OP"
Case Is = "B"
myStr = "Admitted with presumed acute need; " _
& "Documentation and findings did not " _
& "support IP status. Should have been " _
& "OP observation"
Case Is = "C"
myStr = "No IP acuity documented; no complication " _
& "post procedure or acute intervention; " _
& "should have been OP Surgery."
Case Is = "D"
myStr = "Patient does not meet IP guidelines; " _
& "should be OP observation"
End Select
If myStr = "" Then
'do nothing
Else
.Cells(iRow, "BI").Value = myStr
End If
End If
Next iRow
End With
End Sub

And did I get that column BI correct? 36 from column Y???




akemeny wrote:

I need the macro below to look first at Range BI6:BI137 and find blank cells,
then look at Range Y6:Y137 for the code information. Is that possible?

Sub Sonic()
With Worksheets("Oct 8 - 2054543")
For Each c In Range("y6:y137")
If c.Value = "A" Then
c.Offset(0, 36).Value = "No medical necessity for IP status; should
have been OP"
End If
If c.Value = "B" Then
c.Offset(0, 36).Value = "Admitted with presumed acute need;
Documentation and findings did not support IP status. Should have been OP
Observation"
End If
If c.Value = "C" Then
c.Offset(0, 36).Value = "No IP acuity documented; no complication
post procedure or acute intervention; should have been OP Surgery."
End If
If c.Value = "D" Then
c.Offset(0, 36).Value = "Patient does not meet IP guidelines; should
be OP observation"
End If
End With
End Sub

--

Dave Peterson


--

Dave Peterson