selecting ranges in excel using VBA

Posted by David Schlessinger on January 16, 2002 5:27 PM

This is driving me absolutely crazy. I can't write VB that will select a range of cells using the cells property. I even copied the code out of the help menu and I still get Run Time Error '1004', Application Defined or Object Defined error. The code I copied is this:
Worksheets("Macro").Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
Selecting ranges with just the range property (range("a1:a4") works, but I my ranges need to include integer variables that will change. What is going on here? Is there a different way to select variable ranges?

Dave

Posted by Bariloche on January 16, 2002 6:35 PM

David,

Step through this code and see if it answers your question:

Sub SelectRange()
    Dim MyRow As Long
    Dim MyColumn As Long

    MyRow = 1
    MyColumn = 1

    Range(Cells(MyRow, MyColumn), Cells(MyRow + 4, MyColumn + 2)).Select
    Selection.Font.FontStyle = "Italic"
End Sub

enjoy

Posted by Tom Dickinson on January 16, 2002 7:30 PM

Start with the simble:
Range("'Macro spreadsheet'!A1","B2")
Now you want to change the row number to a variable:
Range("'Macro spreadsheet'!A" & variable1,"B" & variable2)
You want to change the column number:
Range("'Mss'!" & chr(64 + column variable) & "1","B2")
(careful not to go beyond letter Z [26])

Otherwise you could specify A1 and offset the number of rows and columns necessary.