CODEWITHSUNDEEP
vbaexcelexcel-2007
Dopeyy2
Dopeyy2

Reputation: 27

What do I set "Dim y As"?

So what I'm trying to do is order my list 1 through whatever the bottom # is. Within doing so I'm in a way moving all of my information to the right. Then I'm going to the bottom of my selection to find the bottom of my list in column B. and then left 1, to find what the bottom A column to fill in a # system all the way down to the bottom of the list.

Private Sub CommandButton2_Click()
       'My problem is i don't know what to set "Dim y As Range" I know Range is 
       'incorrect along with Long, and Integer.
    Dim y As Range
    Sheets("PalmFamily").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("A1:A3").Select
       'As you can notice I also have a weird code 
       '" y = Range("B1").End(xlDown).Offset(0, -1)" I'm trying to go to the bottom 
       'of my list then move left 1. 
    y = Range("B1").End(xlDown).Offset(0, -1)
       'As well the I'm not sure if I set my range to be correct when I do
       '"Range("A1,y")"
    Selection.AutoFill Destination:=Range("A1,y"), Type:=xlFillDefault
    Range("A1,y").Select
End Sub

Upvotes: 0

Views: 115

Answers (1)

David Zemens
David Zemens

Reputation: 53623

The code you posted does not compile, I can tell that just by looking at it.

You have declared y As Range which is an object, which requires the Set keyword when assigning.

Set y = Range("B1").End(xlDown).Offset(0, -1)

Furthermore, Range("A1,y") is a syntax error that I see in two places. Both of these, I think, should be changed to :

Range("A1", y)

Note that the comma and the "y" are not enclosed in the quote marks.

Upvotes: 2

Related Questions