Discussion Locked

VB - variable name substitition

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have the following code. This example is actually from VBA, but the question is a general VB one.

For x=1 to NameCounter
NameRef="plant" + trim(str(x)) + "name"
set NameRef = Range(Cells(begindatarow,namecol),Cells(enddatarow,namecol))
Next

I need to modify this code so that the range in the Set statement is assigned to the value of the variable
NameRef and not "NameRef" literally.
For example: plant1name=Range(Cells(begindatarow,namecol),Cells(enddatarow,namecol))

5 replies

by mark.thomson

Try this

For x = 1 to NameCounter
NameRef.name = ="plant" + trim(str(x)) + "name"
NameRef.Refersto = "=Sheet1!A1:A10"
Next x

where begindatarow =1 etc

you may have to explicitly declare NameRef as being of type Excel.Name and manage it correctly, if you aren't already doing so.

The general case you are looking for is to set the Name property of the object as well as the default property for the object.

This discussion is closed to new replies.