98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » NameRange
NameRange
Resolved · Medium Priority · Version 2007
Thu 15 Jul 2010, 14:51Delegate Rita said...
Rita has attended:
Excel VBA Intro Intermediate course
Presentation Skills course
NameRange
Dear Anthony,
Thank you for your assistance yesterday. I really enjoyed your training course.
Back to work today things appear much more difficult though!
I am trying to export some data from several spreadsheets and a colleague told me that if I use name ranges would be easier to track which data is moved and where. In particular I have a little table of values. Would be possible in VBA give a name range to the columns and rows of the table and then identify each element of the matrix?
Many thanks and regards,
Rita
Thu 15 Jul 2010, 18:46Trainer Anthony said...
RE: NameRange
Hi Rita, thanks for your query and your kind words. You can create a named range in VBA using this type of code:
ActiveWorkbook.Names.Add Name:="myrangename", RefersTo:=Range("a1:a9")
Once you've done that, you can use the named range like this:
Dim mycells As Integer
mycells = Sheets("SalesReport1").Range("myrangename").Count
MsgBox mycells
However, when you talk about identifying each element of a matrix you're that's slightly different. You can feasibly loop through a named collection of cells but more specifically you're talking about what's called an array. You can certainly identify elements in an array using a similar method to R1C1 notation, but we cover that sort of thing on our advanced course so we didn't talk about that when we met. However, if you give me some more information about the type of thing you're trying to achieve I'll try to give you a workaround.
All the best,
Anthony
Tue 20 Jul 2010, 12:22Delegate Rita said...
RE: NameRange
Hello Anthony,
Thank you very much for your help. I have used your command and it works!
The data I want to import into another spreadsheet is ia a series of source files that can be modified in the future. Therefore I would like to assign a name to each cell of interest in such a way I know what data I am importing and also make the data transfer independent from any structure change in the source file (e.g. adding new lines). I have a table (actually a matrix 20 x 3) that lists names of turbines against the costs of the corresponding mechanical drive and generator set. If I give a name range to the rows (e.g. TurbManuf) and a name range to the 2 columns of costs (CostMechDrive and CostGenSet) how can I identify each element?
Many thanks for your help.
Best regards,
Rita
Tue 20 Jul 2010, 17:57Trainer Anthony said...
RE: NameRange
Hi Rita, glad the previous code worked out. I think what you need in your 20x3 matrix is a fourth column, which could be hidden if needs be. This would hold what database users call a "primary key", an ID for each three-field record - it could be ID1, ID2, ID3, etc. Then when you move your data around you take the primary key field with each piece of data (so a CostMechDrive value needs to have ID3 carried with it) so you can always identify that piece of data and relate it back to the corresponding associated data (say CostGenSet). This might sound fiddly and that I'm suggesting you alter the structure of your data but practically it is a solution.
In code, you could potentially create a 20x3 array in memory and load each value from your table into the area and identify the elements as myarray(10,3). We cover that sort of thing on the advanced course but your small table of data would be a good place to start experiment. Try out the primary key idea first and see how you get on.
Hope this helps,
Anthony
Tue 27 Jul 2010: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Applying and removing border from cell in Excel 2010Did you know the shortcut key for applying and removing the outline border for a cell? |
