CODEWITHSUNDEEP
excelvba
M.Ustun
M.Ustun

Reputation: 289

How to create a dynamic range variable

I'm trying to write to cells in a certain range inside a column.

Range("B7:B(noOfTests)") = "Not Tested"

What am I missing? Is there a better practice for what it's trying to achieve?

Upvotes: 3

Views: 358

Answers (3)

Samuel Hulla
Samuel Hulla

Reputation: 7109

To elaborate on @Rory 's answer, or as to why your code doesn't work:

The double quotes " symbolize a String literal in VBA (and almost any other programming language).

The way a Range object works, is that you specify 1, or optionally 2 cells. Where Cell 1 is the starting cell (and ending cell if not specified) and Cell 2 is the ending cell, defining your selected range.

There are multiple ways of passing the arguments to a range, the most common is however to use the above-mentioned string literal. eg. Range("B7:B12") < note the quotes inside brackets

What you're however trying to do, is to dereference a variable's value inside a string literal. This is achieve in VBA via the ampersand & expression.

This what you're trying to do (if interpret could read a pseudocode)

Range("B7:B"<insert row value>) = "Not Tested"

What you're however doing instead is literally inputting your variable as part of your string, so:

Range("B7:BnoOfTests") = "Not Tested"

Obviously the interpeter is confused. BnoOfTests is not a common Cell referencing format and it doesn't know what to make of it. What you need to do is dereference your variable into a value as I hinted above, so the correct solution is:

Range("B7:B" & noOfTests) = "Not Tested"

Other good practice to avoid the string referencing confusion is to refer to the two cells that define your range. Much like I literally described above:

The way a Range object works, is that you specify 1, or optionally 2 cells. Where Cell 1 is the starting cell (and ending cell if not specified) and Cell 2 is the ending cell, defining your selected range

So to make sure you don't mess it up, you can use the following definition:

Range(Cells(7, "B"), Cells(noOfTests, "B")) = "Not Tested"

In my humble opinion, this is actually clearer transcription and furthermore, it is a lot less error prone!

Upvotes: 2

user4039065
user4039065

Reputation:

Here is an alternate.

Range("B7").resize(noOfTests, 1) = "Not Tested"

Upvotes: 2

Rory
Rory

Reputation: 34075

You need:

Range("B7:B" & noOfTests) = "Not Tested"

Upvotes: 5

Related Questions