1

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!

2
  • 2
    On what line, do you get the error? Commented Jan 18, 2010 at 17:03
  • 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) Commented Jan 21, 2010 at 23:23

1 Answer 1

1

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

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

1 Comment

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.