Selecting Multiple Rows/Columns to use with a Macro

[andby]

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-ignoreadding; 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-ignoreadding; 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

 

[puru.sve]

Hi ,

You can below code for assign your function for new status.

Sub SelectRow()
Dim lr As Integer
lr = 2
While (Range("A" & lr).Value <> "")

Range("E" & lr).Value = DUP(Int(Range("A" & lr).Value), Int(Range("B" & lr).Value), Range("C" & lr).Value)
lr = lr + 1

Wend

End Sub



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

 

[andby]

Still Need Help

That code did not work: I have outlined what I need to select
and where I am entering the Macro.
Thanks for your help!!!!

<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-ignoreadding; 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;} .xl67 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl68 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:none;} .xl69 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl70 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl71 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl72 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl73 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;} .xl74 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} --> </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="xl67" style="height:15.0pt" height="15">0</td> <td class="xl68">0</td> <td class="xl69">1</td> <td class="xl66">w</td> <td class="xl66">#VALUE!</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl70" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl71">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl72" style="height:15.0pt" height="15">0</td> <td class="xl73">0</td> <td class="xl74">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>



Sub SelectRow()
Dim lr As Integer
lr = 2
While (Range("A" & lr).Value <> "")

Range("E" & lr).Value = DUP(Int(Range("A" & lr).Value), Int(Range("B" & lr).Value), Range("C" & lr).Value)
lr = lr + 1

Wend

End Sub




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