200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How do I select a range that is defined by cell variables. I've tried variants of this:
Sheets(ws_output).cells((1+next_row,next_col):(5+next_row:next_col)).Select
but I can't get it to work. This particular line is a sytax error and this one:
Sheets(ws_output).cells((1+next_row,next_col):(5+next_row:next_col)).Select
is "application defined or object defined error".
with Worksheets(ws_output).Cells(next_row + 1, next_col).Resize(2, 1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End WithNote that there is a difference between the Sheets collection and the Worksheets collection. If you are interested, look it up on the Internet. But for the purpose of this thread I suggest you make it a rule to talk about the things you know rather than the things you don't understand. You know what a worksheet is.
Object collection items are addressed by their index number. The Worksheets collection is a collection of worksheet objects. Therefore you can address each item in the collection by its index number. Numbers always start from one and end with the Count property. The largest available index is, therefore, Worksheets.Count. If you call for a number greater than Count a Subscription Error will occur.
Instead of the Index number you can also use the Name property. In fact, for worksheets, this is more common. The name is a variable of String data type. It could be a hard-coded string or a variable of string data type. So, what is "ws_output" in your code? If you have declared ws_ouput as a string, Worksheets(ws_output) will identify a worksheet. If a sheet by that name doesn't, or the variable hasn't been assigned a value, exist a Subscription Error will result.
VBA allows you to use objects on the fly without naming them. But if you encounter a problem like the one that brought you here it's better to take none of the offered shortcuts. This is the code you should use. It would allow you to see, line by line, where the error occurs.
Dim ws_output As String
Dim Ws As Worksheet
ws_output = "My Output Tab"
Set Ws = Worksheets(ws_output)All the cells in a worksheet are members of the Cells collection. Therefore you could address them by their index number. But it's easier to address them by their coordinates. This is the syntax: Cells([Row number], [Column number]). You can also use the column name but names, of course, are strings and strings are hard to do calculations with. Therefore a number is preferable. But the following two examples are exquivalent.
Cells(10, 2)
Cells(10, "B")Each number can be represented by a reference or a formula. Commonly used is Rows.Count which you recognize. It returns the number of members in the Rows collection, which is 1048576. Therefore Cells(Rows.Count, "A") is the last cell in column A, or A1048576, and Cells(Rows.Count, "A").End(xlUp) is the first used cell looking from row 1048576 up, which is the last used cell in that column if you look from the top.
If you have a variable Next_Row (underscores in variable names went out of fashion in 1986), which should be of Long data type because an Integer cant hold the number 1048576, you can address a cell Cells(Next_Row, "A"). And if you also have a Long variable called SameClm you can address a cell as Cells(Next_Row, SameClm). Variations like Cells(Next_Row +5, SameClm) are legal and often used like Cells(Next_Row + R, SameClm). But it's essential that you maintain the basic syntax for cell addressing, e.g. Cells([Row number], [Column number]).
You can define a range by specifying its first and last cells. Range(Range("A1"), Range("B2")) would do that. But such an address is hard to calculate. Therefore one would use this instead:-
Set MyRange = Range(Cells(1, 1), Cells(2, 2))Again, remember the basic syntax for each cell and for the range when you build complicated constructions.
And now, as to the last word in your code I'm happy to inform you that you don't need it. The Select statement is strictly for the user to communicate with the worksheet. Your PC owns the worksheet and knows exactly where every part of it is. You don't need to point at it. Just tell the computer which cell you mean and you can read from it or write to it without ever "selecting" anything. Use the Select statement when you want to hand control back to the user. Like "Please write your name in the selected cell", and then you select a cell for him.
So, your code might intend somethig like I show below. That would delete all contents of the range you specify.
Worksheets(ws_output).Cells((next_row + 1,next_col), (Next_row + 5, next_col)).ClearContents