VBA Macro - Search and Return Help

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>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top