Why isn't this statement working? Seems simple enough.

[pglufkin]

I have

reg is defined as a range

Set reg = Worksheets("DataSet").Range(Cells(M, 9), Cells(NN, 9))

And it gives me back "Application-defined or object-defined error. The variables M and NN contain correct Values.

??? Weird, this should work in my mind.

 

[Dryver14]

Are you trying to define 2 seperate ranges or should it be .Range("M9:NN9")?

 

[circledchicken]

I have

reg is defined as a range

Set reg = Worksheets("DataSet").Range(Cells(M, 9), Cells(NN, 9))

And it gives me back "Application-defined or object-defined error. The variables M and NN contain correct Values.

??? Weird, this should work in my mind.

Hi,

Can you share more of the code you have?
How are M and NN defined and what values do they hold when you get the error?

 

[Rick Rothstein]

I have

reg is defined as a range

Set reg = Worksheets("DataSet").Range(Cells(M, 9), Cells(NN, 9))

And it gives me back "Application-defined or object-defined error. The variables M and NN contain correct Values.

??? Weird, this should work in my mind.

The two Cells calls are pointing to the ActiveSheet, not the DataSet worksheet. Try it this way...

With Worksheets("DataSet")
  Set reg = .Range(.Cells(M, 9), .Cells(NN, 9))
End With

or, alternately, Dim a variable (I'll name it WS for this example) as as Worksheet data type to hold the reference to the worksheet and then do this...

Set WS = Worksheets("DataSet")
Set reg = WS.Range(WS.Cells(M, 9), WS.Cells(NN, 9))

 

[pglufkin]

Still cannot get it to work even with implementing the above . . . . reg will not take on the range parameters.

 

[pglufkin]

More of my code.

(code)
Declarations before any subs
Dim reg As Range
Dim tar As Range
Dim WS As Worksheet
Dim DataVar As Range



Sub rangeset()
Dim N As Long, NN As Long
Dim M As Long, col As Long
Dim target As Range, source As Range
Dim s As String
Application.Calculation = xlCalculationManual
Set tar = ActiveCell
col = 9
N = 30
M = Range("DataVar").Row + 1
For NN = M To N
s = Worksheets("DataSet").Cells(NN, col).Value
If Asc(Right(s, 1)) = 63 Then
Set WS = Worksheets("DataSet")
Set reg = WS.Range(WS.Cells(M, 9), WS.Cells(NN, 9))
Exit For
End If
Next NN
If IsNumeric(Left(ActiveCell.Text, 1)) = True Then
ActiveCell.Offset(1, 0).Select
End If
Set target = ActiveCell
Set source = reg
ConcatAndColorParts target, reg
End Sub
(\code)

 

[pglufkin]

However source is fine, I fixed it . . . for some reason reg is not getting the range, but source is . . . weird.

 

[Teeroy]

If M is greater than N your code will fail as you skip the For/Next loop then try to set a Range equivalent to an object that doesn't exist (reg is only set inside the loop). If you really need to loop between 2 variables of unknown sizes there are some methods you can try:

For NN =WorksheetFunction.Min(M, N) to WorksheetFunction.Max(M, N)

or

If M > N Then x = -1 Else x = 1
For NN = M To N Step x