PDA

View Full Version : [SOLVED] quick help on using a variable to define a range



jmutsche
12-09-2013, 02:01 PM
Sub Macro3()

Dim myrng As Range

With ActiveSheet
If .AutoFilterMode Then
.ShowAllData
End If
End With

ActiveSheet.AutoFilter.Sort.SortFields. _
Clear
ActiveSheet.AutoFilter.Sort.SortFields. _
Add Key:=Range("M1:M51816"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

x = 2
Cells(x, 13).Select

Do While Selection.Value <> ""
Cells(x, 13).Select
Selection.Value = Selection.Value




x = x + 1

Loop

myrng = "Range(" & "A2" & ":R" & x - 2 & """)"
Debug.Print myrng
myrng.Select
' Range("A2:R19").Select
'Cells(x - 2, 3).EntireRow.Select

'Range(Selection, Selection.End(xlUp)).Select



I am getting error 91 object variable or with block variable not set. I don't get it. can someone please help?

jmutsche
12-09-2013, 02:16 PM
I am getting error 91 object variable or with block variable not set. I don't get it. can someone please help?

jmutsche
12-09-2013, 02:23 PM
ha nvm didn't set my range my bad

mancubus
12-09-2013, 03:08 PM
not sure what you're trying to accomplish :)



Sub Macro3()


Dim MyRange As Range
Dim LastRow As Long

With ActiveSheet
.Range("A1").CurrentRegion.Sort Key1:=.Range("M2"), Order1:=xlAscending, Header:=xlYes
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
With .Range("M2:M" & LastRow)
.Value = .Value
End With
Set MyRange = .Range("A2:R" & LastRow - 2)
'codes related with MyRange
'....
'...
End With

End Sub

SamT
12-09-2013, 11:29 PM
Set myRng = Range("A2:R" & CStr(x-2))
'or
Set myRng = Range(Range("A2"), Cells(18, x-2))
'or several other ways

You must SET a variable to an Object.

You can format the code in your post by either selecting all the code and pressing the # icon, or by pressing the # icon and pasting your code in between the new CODE Tags. Finally, you can type the opening and closing tags before and after your code. "Edit" your post to see an example

SamT
12-09-2013, 11:37 PM
This is a merged version of two different threads. all the posts in both threads are displayed in chronological order in this thread, regardless of which original thread they were posted in.