How do I set a range using integer variables?

[JoeBobJenkins]

I've tried to do this about a dozen different ways but I can't seem to get the range to set to the values I need. Any thoughts why?

Public Sub Add2Excel(varDataArray() As Variant)

Dim xlappApp As Excel.Application
'Set xlappApp = GetObject("Excel.Application")
Set xlappApp = CreateObject("Excel.Application")
Dim wkbkWkbk As Excel.Workbook
Set wkbkWkbk = xlappApp.Workbooks.Add

Dim wkshtData As Excel.WorkSheet
Set wkshtData = wkbkWkbk.ActiveSheet
wkshtData.Name = "All Data"
Dim intRowCount, intColCount As Integer
For intColCount = LBound(varDataArray) To UBound(varDataArray)
    For intRowCount = LBound(varDataArray, 2) To UBound(varDataArray, 2)
        wkshtData.Cells(intRowCount + 1, intColCount + 1).Value = varDataArray(intColCount, intRowCount)
    Next intRowCount
Next intColCount
'Deskttop Path
Dim DesktopPath As String
DesktopPath = Environ("USERPROFILE") & "\Desktop"
'Write to Text file for records
Dim FilePath As String
FilePath = DesktopPath & "\OccTestRuns.xlsx"
wkshtData.Columns("A:AZ").EntireColumn.AutoFit
Dim rng2Sort As Range
    Set rng2Sort = wkshtData.Range(Cells(1, 1), Cells(intRowCount, intColCount)).Select
    ActiveWorkbook.Worksheets("All Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("All Data").Sort.SortFields.Add Key:=Range( _
        "B2:B691"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("All Data").Sort
        .SetRange Range("A1:I691")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
wkbkWkbk.SaveAs (FilePath)
End Sub

 

[RoryA]

Welcome to the forum.

You need to specify the sheet for the Cells properties as well as the Range one:

Set rng2Sort = wkshtData.Range(wkshtData.Cells(1, 1), wkshtData.Cells(intRowCount, intColCount)).Select

A couple of other points:
1. Use Long not Integer for row variables.
2. This:

Dim intRowCount, intColCount As Integer

only declares intColCount as an Integer. intRowCount is actually a Variant. You have to specify the type for each variable like this:

Dim intRowCount As Integer, intColCount As Integer

But, again, use Long, not Integer.

 

[JoeBobJenkins]

Welcome to the forum.

You need to specify the sheet for the Cells properties as well as the Range one:

Set rng2Sort = wkshtData.Range(wkshtData.Cells(1, 1), wkshtData.Cells(intRowCount, intColCount)).Select

A couple of other points:
1. Use Long not Integer for row variables.
2. This:

Dim intRowCount, intColCount As Integer

only declares intColCount as an Integer. intRowCount is actually a Variant. You have to specify the type for each variable like this:

Dim intRowCount As Integer, intColCount As Integer

But, again, use Long, not Integer.

Thanks!, and didn't know that about declaring variables being variants. Need to do a lot of redeclaring elsewhere.