1

I'm looking for a way to, instead of typing "ActiveCell.OffSet(1,1) over and over again in my vba code, define that as a variable, "x" and use that instead.

I have to use the dim command to do this but I"m not sure what the data type would be.

Suggestions?

When I test it using the code below I get Runtime Error 1004.

Private Sub CommandButton1_Click()
    Dim i As Range
    Set i = ActiveCell

     ActiveSheet.Range(ActiveSheet.Range(i), ActiveSheet.Range(i).End(xlUp)).Select

End Sub
Hugh_Kelley
  • 988
  • 1
  • 9
  • 23

2 Answers2

3

In response to your edit

Avoid the use of .Select/Activate and fully qualify your objects. INTERESTING READ

Your code can be written as

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range

    '~~> Change as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set rng1 = ws.Range("A10")

        Set rng2 = .Range(rng1, rng1.End(xlUp))

        With rng2
            Debug.Print .Address
            '
            '~~> Do something with the range
            '
        End With
    End With
End Sub

If you still want to know what was wrong with your code then see this.

You have already defined your range. You do not need to add ActiveSheet.Range() again. Your code can be written as

Private Sub CommandButton1_Click()
    Dim i As Range

    Set i = ActiveCell

    ActiveSheet.Range(i, i.End(xlUp)).Select
End Sub

EDIT

Followup from comments

Was ActiveSheet.Range() actually problematic or just redundant? – user3033634 14 mins ago

It is problematic. The default property of a range object is .Value

Consider this example which will explain what went wrong with your code

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set rng = .Range("A1")

        rng.Value = "Blah"

        MsgBox rng           '<~~ This will give you "Blah"
        MsgBox rng.Value     '<~~ This will give you "Blah"
        MsgBox rng.Address   '<~~ This will give you "$A$1"

        MsgBox ws.Range(rng) '<~~ This will give you an error
        '~~> Why? Becuase the above is evaluated to
        'MsgBox ws.Range("Blah")

        MsgBox ws.Range(rng.Address) '<~~ This will give you "Blah"
    End With
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Was ActiveSheet.Range() actually problematic or just redundant? – Hugh_Kelley Nov 27 '13 at 20:15
  • 1
    Let me explain. The default property of a range is `.Value` so when you say `ActiveSheet.Range(i)` then it mean `ActiveSheet.Range(VALUE OF I)` and not `ActiveSheet.Range(ADDRESS OF I)` – Siddharth Rout Nov 27 '13 at 20:18
  • See the updated post. I have explained why is it `Problamatic` – Siddharth Rout Nov 27 '13 at 20:31
  • @Shiva: Stop playing [DIRTY](http://stackoverflow.com/questions/20254526/nested-if-then-statement-proper-way-to-handle)... If you have the guts then leave a comment. I knew you would do that so I took a snapshot of your profile. Any more revenge downvotes and I will report you to a mod. – Siddharth Rout Nov 27 '13 at 23:07
0
Dim x As Range
Set x = ActiveCell.OffSet(1,1)

EDIT: in response to your comment:

Private Sub CommandButton1_Click()
    Dim i As Range
    Set i = ActiveCell
    ActiveSheet.Range(i, i.End(xlUp)).Select
End Sub
rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • Range makes sense, I've updated my question so that you can see the context I'm trying to use it in. My goal is to select the contiguous range above the active cell. Any thoughts? – Hugh_Kelley Nov 27 '13 at 20:02