SRMPURCHASE
Board Regular
- Joined
- Dec 23, 2014
- Messages
- 210
- Office Version
- 2016
- Platform
- Windows
In the following formula, whenever I sort the range with this formula (and others with similar problem), the lookup value will change by 3 - 7 rows after the sort is finished.
The range is sorted with the VBA following the Index formula. Is there a conflict between the two that causes the lookup value to not keep its relative position after the sort is finished?
=IFERROR(INDEX('PLANT ARO LIST'!$D$3:$D$41,MATCH('PO LOG'!J188,'PLANT ARO LIST'!$B$3:$B$41,"0")),"")
Sub POLOGSORT()
'
' POLOGSORT Macro
'
'
Range("B25:BJ300").Select
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Add Key:=Range( _
"B25:B300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Add Key:=Range( _
"E25:E300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("PO LOG").Sort
.SetRange Range("B25:BJ300")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("BE25").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F300").End(xlUp).Select
End Sub
The range is sorted with the VBA following the Index formula. Is there a conflict between the two that causes the lookup value to not keep its relative position after the sort is finished?
=IFERROR(INDEX('PLANT ARO LIST'!$D$3:$D$41,MATCH('PO LOG'!J188,'PLANT ARO LIST'!$B$3:$B$41,"0")),"")
Sub POLOGSORT()
'
' POLOGSORT Macro
'
'
Range("B25:BJ300").Select
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Add Key:=Range( _
"B25:B300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("PO LOG").Sort.SortFields.Add Key:=Range( _
"E25:E300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("PO LOG").Sort
.SetRange Range("B25:BJ300")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("BE25").Select
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F300").End(xlUp).Select
End Sub