CODEWITHSUNDEEP
vbaexcel
Jongscx
Jongscx

Reputation: 70

Can you give columns a "variable name" for a specific sheet in Excel VBA

Ok, so I have read this:

Can you give columns in Excel a variable name?

and from my understanding, I can essentially setting "Name" to equal A, such that when I do:

For i = 1 to 100

worksheets(Sheet1).Range(Name & i).Value = "poop"

next i

It will fill Cells A1:A100 with poop.

However, if I were to type:

worksheets(Sheet2).Range(Name & i).Value = "poop"

it would also fill A1:A100, except in Sheet 2 now.

Is there any way where "Name" is Sheet-specific? Something like:

Sheet1.Name = "A"
Sheet1.Name = "F"
For i = 1 to 100

worksheets(Sheet1).Range(Name & i).Value = "poop"
worksheets(Sheet2).Range(Name & i).Value = "poop"

Would result in sheet1 having column A full of poop and sheet 2 have column F full of poop. Maybe I'm being too picky because I know I can type "Sheet1.Name" and "Sheet2.Name" because they're of that object, but I'm trying to avoid that since I've already specified it in Worksheets() and it would make the code cleaner.

I was thinking I'd have to put it in to each sheet's module, maybe as a private because they are both called "Name", but I really don't know...

Upvotes: 1

Views: 1341

Answers (2)

JNevill
JNevill

Reputation: 50034

You'll want to create a new range object and then set that range object. A range can be a set of cells or a column or a row:

Dim rngCol as Range
Dim rngGroup as Range

Set rngCol = Worksheets("Sheet1").Range("A:A")
Set rngGroup = Worksheets("Sheet1").Range("A1:F100")

rngCol.Value = "Poop"
rngGroup.Value = "Poop"

Or you can iterate through these after setting them:

For each rngCell in rngCol
     rngCell.Value = "Poop"
Next rngCell

Upvotes: 0

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

Code:

Worksheets(Sheet1).Range("A:A").Name = "PoopRange"

Then:

Range("PoopRange") will always refer to Sheet1, Column A.

You can use Range("PoopRange").Cells(i,1) to loop through each cell in that column.

Upvotes: 1

Related Questions