Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
You can use SpecialCells to find the blanks, but you need to expand a little on what you are asking. What codes do you want to find? Code: -------------------- Dim rBlanks With Worksheets("Oct 8 - 2054543") Set rBlanks = .Range("BI6:BI137").SpecialCells(xlCellTypeBlanks) -------------------- -- royUK Hope that helps. RoyUK ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=25419 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
I like doing this using row Numbers and Select Case
Sub Sonic() With Worksheets("Oct 8 - 2054543") For RowCount = 6 to 137 Select Case .Range("Y" & Rowcount) case "A": Range("BI" & Rowcount).Value = "No medical necessity for IP status; should have been OP" case "B": Range("BI" & Rowcount).Value = "Admitted with presumed acute need; Documentation and findings did not support IP status. Should have been OP Observation" case "C": Range("BI" & Rowcount).Value = "No IP acuity documented; no complication post procedure or acute intervention; should have been OP Surgery." case "D": Range("BI" & Rowcount).Value = "Patient does not meet IP guidelines; should be OP observation" end select End With End Sub "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
The codes are the alpha letters that we use to identify which item
description will be pulled from the VBA. We enter the letter into the Y cell and when we activate the page next time the item description will be placed in the corresponding BI cell. However, there are times when we need to make minor adjustments to the BI cell data (regarding dates or added information). Thats why I need the code to look for the blank cells in BI then look for the letter in Y before pulling the data. "royUK" wrote: You can use SpecialCells to find the blanks, but you need to expand a little on what you are asking. What codes do you want to find? Code: -------------------- Dim rBlanks With Worksheets("Oct 8 - 2054543") Set rBlanks = .Range("BI6:BI137").SpecialCells(xlCellTypeBlanks) -------------------- -- royUK Hope that helps. RoyUK ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=25419 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can this Macro be modified?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modified:- Macro to Generate a new Column!!! | Excel Worksheet Functions | |||
Modified:- Macro to Generate a new Column!!! | Excel Worksheet Functions | |||
Modified worksheet macro, where if Yes portion works but not else | Excel Programming | |||
Date last modified macro | Excel Programming | |||
How to record macro to print pages modified today | Excel Worksheet Functions |