Fill and Sort

[kenpcli]

How do I get it to keep filling in the location number? It breaks after it fills the first site.


[TABLE="width: 704"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]47[/TD]
[TD]Totals For MEDICARE RR[/TD]
[TD]$37,253.00[/TD]
[TD]-$15,900.45[/TD]
[TD]-$20,182.52[/TD]
[TD]$0.00[/TD]
[TD]$1,170.03[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For MOLINA[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For MULTIPLAN[/TD]
[TD]$9,156.00[/TD]
[TD]-$4,661.86[/TD]
[TD]-$1,026.14[/TD]
[TD]$0.00[/TD]
[TD]$3,468.00[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For SELF PAY[/TD]
[TD]$80,687.75[/TD]
[TD]-$38,452.48[/TD]
[TD]-$37,242.02[/TD]
[TD]$149.40[/TD]
[TD]$5,142.65[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For TRICARE[/TD]
[TD]$39,590.00[/TD]
[TD]-$18,544.17[/TD]
[TD]-$12,423.73[/TD]
[TD]$921.20[/TD]
[TD]$9,543.30[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For UNITED HEALTHCARE[/TD]
[TD]$237,266.00[/TD]
[TD]-$153,616.60[/TD]
[TD]-$65,164.26[/TD]
[TD]$1,515.56[/TD]
[TD]$20,000.70[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Totals For VETERANS ADMIN[/TD]
[TD]$23,748.00[/TD]
[TD]-$10,461.24[/TD]
[TD]-$10,419.76[/TD]
[TD]$0.00[/TD]
[TD]$2,867.00[/TD]
[/TR]
[TR]
[TD]Totals For Albuquerque[/TD]
[TD][/TD]
[TD]$3,661,122.75[/TD]
[TD]-$1,683,474.79[/TD]
[TD]-$1,780,094.21[/TD]
[TD]$16,942.84[/TD]
[TD]$214,496.59[/TD]
[/TR]
[TR]
[TD]Bellevue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For AETNA[/TD]
[TD]$504,981.00[/TD]
[TD]-$312,837.07[/TD]
[TD]-$173,143.99[/TD]
[TD]$8,490.26[/TD]
[TD]$27,490.20[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For CIGNA[/TD]
[TD]$201,832.00[/TD]
[TD]-$137,141.51[/TD]
[TD]-$43,931.99[/TD]
[TD]$546.69[/TD]
[TD]$21,305.19[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For COMMERCIAL[/TD]
[TD]$314,544.00[/TD]
[TD]-$221,865.05[/TD]
[TD]-$27,915.65[/TD]
[TD]$0.00[/TD]
[TD]$64,763.30[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For COMMERCIAL FOUNDATIONS
[/TD]
[TD]$11,292.00[/TD]
[TD]-$3,000.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$8,292.00[/TD]
[/TR]
</tbody>[/TABLE]


Sub FillDownLookup()


Dim Ar As Areas
Dim Rng As Range
Dim ValU As Long

With Sheets("Analysis")
Set Ar = .Range("A6:A" & .Range("C" & Rows.Count).End(xlUp).row).SpecialCells(xlBlanks).Areas
For Each Rng In Ar
Rng.Value = Evaluate("VLookup(" & Rng.Offset(-1).Resize(1).Address & ",'LEGEND SITE'!A1:B20, 2, False)")
Next Rng
.Columns(1).Insert
With Columns(2)
.SpecialCells(xlConstants, xlTextValues).EntireRow.Delete
End With
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("C6", .Range("C" & Rows.Count).End(xlUp)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("B6:H" & Range("B" & Rows.Count).End(xlUp).row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

 

[Fluff]

It was a typo in my original code. When I added the With Statement, I obviously missed that bit.
If the "Analysis" sheet is active, it should work, otherwise it might not depending on what is in Col B.

 

[Joe4]

I didn't realize you wrote the code originally - but I found the thread from back in November.
I'll let you handle it then!

 

[Fluff]

OK