gchavez000
New Member
- Joined
- Mar 6, 2017
- Messages
- 5
I have a spreadsheet that has data in rows 14 - 797, and columns A through AM.
Column B has addresses. The next column of important information is in column D through AM.
These columns layout like so: Column D = Landscape Charges, Column E = the z-score for those landscape charges, Column F = pest control, Column G = z-score for pest control charges. This goes so on and so forth through to column AM.
On a separate worksheet i need to run a macro that looks through the z-scores and returns all addresses that exceed the parameter >.20. AS well as which z-score it exceeded. I posted this problem to another board on a very simple example spreadsheet and below is the code i was given. this code works beautifully, but i cannot understand how to make it work in my bigger project. any help is so much appreciated.
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub abc()
Dim a, b, w
Dim i As Long, ii As Long, n As Long
With Worksheets("data")
a = .Range("a4").CurrentRegion
End With
ReDim b(1 To UBound(a), 1 To 2)
For i = 2 To UBound(a)
ReDim w(0)
For ii = 3 To UBound(a, 2) Step 2
If a(i, ii) > 0.2 Then
w(UBound(w)) = a(1, ii)
ReDim Preserve w(UBound(w) + 1)
End If
Next
If Not IsEmpty(w(1)) Then
ReDim Preserve w(UBound(w) - 1): n = n + 1
b(n, 1) = a(i, 1): b(n, 2) = Join(w, ", ")
End If
Next
With Worksheets("extracted data")
.Cells(4, 1).Resize(, 2) = [{"address", "exceeded parameters"}]
.Cells(5, 1).Resize(n, 2) = b
End With
End Sub</code>
Column B has addresses. The next column of important information is in column D through AM.
These columns layout like so: Column D = Landscape Charges, Column E = the z-score for those landscape charges, Column F = pest control, Column G = z-score for pest control charges. This goes so on and so forth through to column AM.
On a separate worksheet i need to run a macro that looks through the z-scores and returns all addresses that exceed the parameter >.20. AS well as which z-score it exceeded. I posted this problem to another board on a very simple example spreadsheet and below is the code i was given. this code works beautifully, but i cannot understand how to make it work in my bigger project. any help is so much appreciated.
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub abc()
Dim a, b, w
Dim i As Long, ii As Long, n As Long
With Worksheets("data")
a = .Range("a4").CurrentRegion
End With
ReDim b(1 To UBound(a), 1 To 2)
For i = 2 To UBound(a)
ReDim w(0)
For ii = 3 To UBound(a, 2) Step 2
If a(i, ii) > 0.2 Then
w(UBound(w)) = a(1, ii)
ReDim Preserve w(UBound(w) + 1)
End If
Next
If Not IsEmpty(w(1)) Then
ReDim Preserve w(UBound(w) - 1): n = n + 1
b(n, 1) = a(i, 1): b(n, 2) = Join(w, ", ")
End If
Next
With Worksheets("extracted data")
.Cells(4, 1).Resize(, 2) = [{"address", "exceeded parameters"}]
.Cells(5, 1).Resize(n, 2) = b
End With
End Sub</code>