To Macro or use Small with VSLookup.

**philipt** · 2013-04-02, 05:12 PM

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** · 2013-04-02, 05:27 PM

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** · 2013-04-04, 01:35 PM

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

Code:

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