Member Avatar for bops

hi everyone, I am doing a project for college, which involves programming using VBA in Excel. I have most of it sorted and i am having trouble on this 1 part. I need to find out how to use variables to access a sheet and range within the spreadsheet

variable1 = sheets("sheet").range("range1")
variable2 = sheets("sheet").range("range2")
txtBox = sheets(variable1).range("variable2")

this would look in "sheets" find "range1" which has the name of another sheet. then it would look in "range2" which has the name of a range, then these are combined looking in the sheet and range given by values in the spreadsheet.

this seems to be erroneous, does anyone have any suggestions ?

  • 2 Contributors
  • 2 Replies
  • 197 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by bops
Member Avatar for mnemtsas

hi everyone, I am doing a project for college, which involves programming using VBA in Excel. I have most of it sorted and i am having trouble on this 1 part. I need to find out how to use variables to access a sheet and range within the spreadsheet

variable1 = sheets("sheet").range("range1")
variable2 = sheets("sheet").range("range2")
txtBox = sheets(variable1).range("variable2")

this would look in "sheets" find "range1" which has the name of another sheet. then it would look in "range2" which has the name of a range, then these are combined looking in the sheet and range given by values in the spreadsheet.

this seems to be erroneous, does anyone have any suggestions ?

That wont work. Firstly,

sheets("sheet").range("range1") returns an object, namely the range named range1 in the worsheet called sheet. So you'd need to do:

Set variable1=sheets("sheet").range("range1")
Set variable2 = sheets("sheet").range("range2")

Secondly,

sheets(variable1).range("variable2")

The sheets object can only be referenced by a sheet name or a sheet index. Not a range which variable1 contains.

Perhaps you could explain the problem you are trying to solve so we can help better.

Member Avatar for bops

It is alright now, i worked on it and it seemed to work, my project is actually more complex than the example i used, but that was simply what the problem was, i have found that it worked, but i had named something wrong. You can use variables as names to work out a given range, since it worked for me :P

I can explain in more detail what the problem was if you want, just let me know, Thanks for your help anyway

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.