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

Which worksheet is active when you run this code? Does it work if you make the "SR060-SR070" worksheet active before you run the code? — barrowc
– barrowc, Commented Jun 19, 2015 at 0:50

Tim Williams · Accepted Answer · 2015-06-19 00:56:40Z

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)

answered Jun 19, 2015 at 0:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

ChipsLetten · Accepted Answer · 2015-06-18 21:35:03Z

1

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.

answered Jun 18, 2015 at 21:35

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

3 Comments

Christian Gatmaitan Over a year ago

Thank you for your response. I am still receiving an issue with the 'Set' line. It says "Method 'Range' of object '_Worksheet' failed.

Pieter Geerkens Over a year ago

@ChristianGatmaitan: Have you stepped through the code with the debugger? Have you examined the intermediate object of the right-hand expression using the Immediate Window and the Watch Window? What are the results of that?

ChipsLetten Over a year ago

What are the values of m and n variables when you get that error?

Collectives™ on Stack Overflow

VBA Range Syntax Issue

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related