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 ?

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.

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

This article has been dead for over six months. Start a new discussion instead.