Obtain 100 percent result .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
701
Office Version
  1. 2016
Platform
  1. Windows
Hello all ,

I am wanting to get a macro to do the following due to having 440,000 rows to go thru .

Cols BL and BP are the results I require where results will always be 100 .

Col BL results come from looking in col BT and If Good then finding all horses in col BJ that
came <=5 . Result shows 15 x .

Col BP results come from looking in col BT for Good , then finding all horses in col BJ that came >=6 ,
then looking in col BK for all that came <=5.5 lenghts away . Result shows only 2 x .

Many thanks .
Excel Workbook
BJBKBLBPBT
1PlcMgGdPlGdMgGood
21212.7Good
31112.3Good
435100Good
579.9Good
623100Good
745.1100Good
8810Good
91011.8Good
10FFGood
1113100Good
1258.6100Good
1369.1Good
14910.5Good
1556.2100Good
1679Good
1713100Good
1868Good
1923100Good
2035.8100Good
2146.1100Good
22LRLRGood
2310.1100Good
24910.3Good
2532.4100Good
2675.1100Good
2743.4100Good
2888.8Good
2954.4100Good
3020.1100Good
3164.5100Good
Sheet1
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Excel Workbook
BJBKBLBMBN
1PlcMgGdPlGdMgGood
21213  Good
31112Good
435100Good
579.9Good
623100Good
745.1100Good
8810Good
91012Good
10FFGood
1113100Good
1258.6100Good
1369.1Good
14911Good
1556.2100Good
1679Good
1713100Good
1868Good
1923100Good
2035.8100Good
2146.1100Good
22LRLRGood
2310.1100Good
24910Good
2532.4100Good
2675.1100Good
2743.4100Good
2888.8Good
2954.4100Good
3020.1100Good
3164.5100Good
Sheet1
 
Upvote 0
I am wanting to get a macro to do the following due to having 440,000 rows to go thru .
See if this does what you want.
Rich (BB code):
Sub Good()
  Dim aData As Variant, aBL As Variant, aBP As Variant, aRws As Variant, aCols As Variant
  Dim lr As Long, i As Long
  
  lr = Range("BT" & Rows.Count).End(xlUp).Row
  aRws = Evaluate("row(2:" & lr & ")")
  aCols = Array(62, 63, 72)                     '<- Columns BJ, BK, BT
  aData = Application.Index(Cells, aRws, aCols)
  ReDim aBL(1 To UBound(aData), 1 To 1)
  ReDim aBP(1 To UBound(aData), 1 To 1)
  For i = 1 To UBound(aData)
    If aData(i, 3) = "Good" Then
      If aData(i, 1) <= 5 Then
        aBL(i, 1) = 100
      ElseIf aData(i, 2) <= 5.5 Then
        aBP(i, 1) = 100
      End If
    End If
  Next i
  Range("BL2:BL" & lr).Value = aBL
  Range("BP2:BP" & lr).Value  = aBP
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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