VBA Range Syntax Issue

Question

I'm trying to create a range to ultimately use in a concatinate function, but I'm having some unknown errors. I have attached my entire code, but the error appears to be during the 'myRange' section. The goal of the code is to divide the steps of a process into 360 minute shifts, and then list all of the parts and tools required (found in column H). Help would be greatly appreciated!

Private Sub CommandButton1_Click()

Dim duration As Integer, n As Long, i As Integer, x As Integer, m As Integer
Dim myRange As Range

n = 3
m = 3
duration = 0
x = 0

For i = 1 To 50

    duration = 0

    While duration < 360

        x = Worksheets("SR060-SR070").Cells(n, "F").Value
        duration = duration + x
        n = n + 1

    Wend

    myRange = Range(Worksheets("SR060-SR070").Cells(m, "H"), Worksheets("SR060-SR070").Cells(n, "H"))
    Worksheets("Shifts").Cells(1, i).Value = ConcatinateAllCellValuesInRange(myRange)
    m = n

Next i

End Sub

Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range) As String


    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourceRange.Cells
    finalValue = finalValue + CStr(cell.Value)
    Next cell

    ConcatinateAllCellValuesInRange = finalValue
End Function

Answer 1

This is problemmatic:

myRange = Range(Worksheets("SR060-SR070").Cells(m, "H"), _
                Worksheets("SR060-SR070").Cells(n, "H"))

The Range() method here has no worksheet qualifier, so it will default to the active worksheet, which - if it's not "SR060-SR070" - will raise an error because your Cells() calls are referencing a different sheet.

This would be better as:

With Worksheets("SR060-SR070")
    Set myRange = .Range(.Cells(m, "H"), .Cells(n, "H"))
End With

(and adding Set as pointed out by ChipsLetten)

Answer 2

When setting an object variable (range, worksheet or workbook for instance) to refer to an object you must use the Set keyword.

Set myRange = Range(Worksheets("SR060-SR070").Cells(m, "H"), Worksheets("SR060-SR070").Cells(n, "H"))

This is ok because because you are setting the Value of the range:

Worksheets("Shifts").Cells(1, i).Value = ConcatinateAllCellValuesInRange(myRange)

In your ConcatinateAllCellValuesInRange function, the For Each cell ... Next cell is fine. You should use the & operator for joining strings though.