andby
New Member
- Joined
- Jun 4, 2012
- Messages
- 22
Dear helpers,
I need some help with my code to select multiple rows and columns to use
with a function within a macro. I basically need to select the rows based on duplicates in the street column and the A,B,Status columns to give me a new Status based on the criteria in the Macro.
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
adding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0;} .xl64 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:0;} .xl65 {mso-number-format:0;} --> </style> Your help is really appreciated!
Sample Excel Sheet
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
adding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:0; text-align:center;} .xl64 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center;} .xl65 {mso-number-format:0; text-align:center;} .xl66 {text-align:center;} --> </style> <table style="border-collapse: collapse;width:304pt" border="0" cellpadding="0" cellspacing="0" width="304"> <colgroup><col style="width:65pt" span="3" width="65"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="44"> <col style="width:65pt" width="65"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:65pt" height="15" width="65">A</td> <td class="xl63" style="width:65pt" width="65">B</td> <td class="xl63" style="width:65pt" width="65">Status</td> <td class="xl64" style="width:44pt" width="44">Street</td> <td class="xl63" style="width:65pt" width="65">New Status</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">1</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">11</td> <td class="xl65">0</td> <td class="xl65">2</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> </tbody></table>
Macro
Function DUP(A, B, Status)
If B > 0 Then
DUP = 3
ElseIf Status = 22 Then
DUP = 3
ElseIf Status = 4 Then
DUP = 4
ElseIf A >= 6 Then
DUP = 2
ElseIf 0 < A And A < 6 Then
DUP = 1
ElseIf A = 0 Then
DUP = 0
Else: DUP = 5
End If
End Function
I need some help with my code to select multiple rows and columns to use
with a function within a macro. I basically need to select the rows based on duplicates in the street column and the A,B,Status columns to give me a new Status based on the criteria in the Macro.
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
Sample Excel Sheet
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">1</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">11</td> <td class="xl65">0</td> <td class="xl65">2</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> </tbody></table>
Macro
Function DUP(A, B, Status)
If B > 0 Then
DUP = 3
ElseIf Status = 22 Then
DUP = 3
ElseIf Status = 4 Then
DUP = 4
ElseIf A >= 6 Then
DUP = 2
ElseIf 0 < A And A < 6 Then
DUP = 1
ElseIf A = 0 Then
DUP = 0
Else: DUP = 5
End If
End Function