944,173 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > VBA - Excel - sheet,range lookup with variables
Ad:
Permalink
Sep 26th, 2005
0

VBA - Excel - sheet,range lookup with variables

Expand Post »
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 ?
Similar Threads
Reputation Points: 23
Solved Threads: 5
Posting Whiz in Training
bops is offline Offline
214 posts
since Aug 2005
Permalink
Sep 27th, 2005
0

Re: VBA - Excel - sheet,range lookup with variables

Quote originally posted by 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 ?
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.
Reputation Points: 16
Solved Threads: 1
Posting Whiz in Training
mnemtsas is offline Offline
200 posts
since Jul 2004
Permalink
Sep 27th, 2005
0

Re: VBA - Excel - sheet,range lookup with variables

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

I can explain in more detail what the problem was if you want, just let me know, Thanks for your help anyway
Reputation Points: 23
Solved Threads: 5
Posting Whiz in Training
bops is offline Offline
214 posts
since Aug 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Another problem.
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Need SQL Help (VB6 and Access)





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC