VB.net Excel sorting

Question

I am trying to get a macro convert from VBA over to vb.net and I am getting a type mismatched error and can't figure it out. I am hoping someone here will be able to help me.

This is the code.

Sub SortRawData() Dim oSheet As Excel.Worksheet Dim oRange As Excel.Range Try oSheet = SetActiveSheet(mLocalDocument, "Sheet 1") oRange = mApplication.ActiveSheet.UsedRange oRange.Sort(Key1:=oRange("J2"), Order1:=Excel.XlSortOrder.xlAscending, _ Header:=Excel.XlYesNoGuess.xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=Excel.XlSortOrientation.xlSortColumns, _ DataOption1:=Excel.XlSortDataOption.xlSortNormal, _ DataOption2:=Excel.XlSortDataOption.xlSortNormal, _ DataOption3:=Excel.XlSortDataOption.xlSortNormal) Catch ex As Exception ErrorHandler.HandleError(ex.Message, ex.Source, ex.StackTrace) End Try End Sub

This is the code from the macro

Sub SortRawData(ByRef poRange As Range) Set poRange = Application.ActiveSheet.UsedRange poRange.Sort Key1:=Range("J2"), Order1:=xlAscending _ , Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal poRange.Sort Key1:=Range("D2"), Order1:=xlAscending, _ Key2:=Range("H2"), Order2:=xlAscending, _ Key3:=Range("L2"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End Sub

Any help would be appreciated.

Thanks!

I am getting a type mismatch error on oRange.Sort(Order1:=Excel.XlSortOrder.xlAscending, _ Key1:=oRange("J2"), _ Header:=Excel.XlYesNoGuess.xlYes, MatchCase:=False, _ Orientation:=Excel.XlSortOrientation.xlSortColumns, _ DataOption1:=Excel.XlSortDataOption.xlSortNormal, _ DataOption2:=Excel.XlSortDataOption.xlSortNormal, _ DataOption3:=Excel.XlSortDataOption.xlSortNormal) — Lora
– Lora, Commented Jan 21, 2010 at 23:23

henryt1975 · Accepted Answer · 2010-01-18 17:41:41Z

1

Simple change the first line like this: oRange.Sort(Order1:=Excel.XlSortOrder.xlAscending, Key1:=oRange("J2")_

answered Jan 18, 2010 at 17:41

henryt1975

212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lora Over a year ago

Thanks, I finally got it to work. Here is my result: oRange.Sort(Key1:=oRange(2, 9), Order1:=Excel.XlSortOrder.xlAscending, _ Header:=Excel.XlYesNoGuess.xlYes, MatchCase:=False, _ Orientation:=Excel.XlSortOrientation.xlSortColumns, _ DataOption1:=Excel.XlSortDataOption.xlSortNormal, _ DataOption2:=Excel.XlSortDataOption.xlSortNormal, _ DataOption3:=Excel.XlSortDataOption.xlSortNormal)

Collectives™ on Stack Overflow

VB.net Excel sorting

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related