To Macro or use Small with VSLookup.

[philipt]

I have a page that I have written a macro for and works great on my computer, but makes their excel stall. I think I can accomplish the same results without using a macro. My goal is to have column J sorted automatically after data is changed. I have a link to the excel document below but my macro is:

Private Sub Worksheet_Calculate()
Set Target = Sheet3.Range("A5:J29").SpecialCells(xlCellTypeFormulas)
Range("A5:J29").Sort _
Key1:=Range("J6"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Is there something wrong with this macro? Otherwise, Can someone recommend a way to have it sort by the Market Basket Average in my page. Perhaps using the Small or Large code. I was using =VLOOKUP($J6,'Data Table'!A2:J25,COLUMN(A1),FALSE) and made a data table on a sheet to hide, but I always got an #NA error.

(Looks like they won't let me post a link yet, so if you can help, PM me and I will send you the URL)

Thanks,
Phil

[NoS]

Suspect different versions of Excel on different computers. If possible try constructing the macro on the other computer and I'd bet it will then work on both.

[JoePublic]

Perhaps you just need to disable events (not real sure why you bother with Target since you don't use it)

Private Sub Worksheet_Calculate()
application.enableevents = false
Range("A5:J29").Sort _
Key1:=Range("J6"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
application.enableevents = true
End Sub

[NoS]

Hello JoePublic,

Circumference of a circle = 2πr²

check out this page http://www.1728.org/diamform.htm

[JoePublic]

Read the footnote

[NoS]

WOW, you should work for our local City Council. That's a fantastic job of deception.