New to VB - Getting Frustrated with Creating Chart

[tegan44]

Using Excel 2007 and Windows XP

So I've programmed before in C, C++, Java, Python, but this VB stuff isn't clicking with me - I know what I want to do, but finding out how to do that from resources seems very difficult. If someone could help me with figuring out what commands I should be using, I would be very appreciative. This post might be slightly long, but hopefully I can get around my problems with minimal help. I have a copy of Excel 2002 VBA to reference, but it has not been easy to navigate either.

My overall goal: to have excel easily make a simplified chart (consisting of different items and those items' main components arranged in rectangles - basically I'm trying to recreate a current chart and make the process automated). Some fields have only one entry (Like a name) and some have multiple (different options available). I wish to make filterable lists as well as the final chart - the chart's purpose being visually seeing the information, while the lists are for easily navigating through/originally storing the data for use in the charts. My plan is to have different worksheet tabs for the different fields and one tab for the chart.

One thing I want to do is to selectively read in information from another excel file. The file I'm reading from has much more information than I need to use. What I want to do is to look down column title "X," until I find a cell equal to "field," then to write the information down in "field" on my chart's appropriate worksheet tab until it reaches the next field. So, Problem 1: How to read info from another file
Problem 2: How to do things like "do...while" and thus move down the column correctly
Problem 3: How to write the info into the appropriate place

Any help would be greatly appreciated, especially specific functions. These are my main problems right now

 

[VoG]

If you record a macro whilst creating the chart manually it should give you the syntax (unless you are using XL 2007).

Also take a look here http://peltiertech.com/Excel/Charts/ChartIndex.html

 

[tegan44]

It's not so much creating the chart that is difficult - I've got a chart template that was given to me. It is more being able to search for a label, and copying the appropriate information, then pasting it as appropriate. - the sort of logical functions that I know how I want to work, but don't know how to execute in excel.

Also, with recording the macros I'm not having luck since I don't know how to say "go here, look for this, copy this information over here, keep going until you hit a blank"
One example of this being trying to record a macro that simply goes two cells down and pastes text - instead of it going two cells down it turns it into "select A5." Or if I search for the given label, go two cells down, copy, and paste - the copy turns into not copying that cell, but copying the word in that specific cell, making it not useful for using for various fields. :-/

 

[VoG]

Can you give a concrete example of what you want to do. I can't write a book on VBA in here

 

[tegan44]

I should clarify, I don't think my use of the term "chart" is the standard excel use of the word "chart" - It's nothing numerical but just listed products and standard options available along with a pretty picture, each product in its own little box with information presented like

name
picture
field 1:
1
2
3
field 2:
a
b
field 3:
.
.
.
etc

 

[tegan44]

Yes - so I want to be able to go down a column and search for a given label. Under that label will be a number of options between 1-5 items long. I want to be able to take those items and copy them to another file along with a code for each product it is related with. I guess my most basic question is how do I go down a column, copy information elsewhere, and have the program know when to stop (i.e. when the next cell has no value). I can create a more complete example if needed....I think for whatever reason this stuff just isn't clicking with me so I'm having difficulties knowing exactly where to go for help. :-/

 

[ChrisM]

You need to use the Range object. Define your column in terms of a range, ie

Sheet1.Range("A1:A10")

Now loop through that range:

Dim rCell as Range

For Each rCell in Sheet1.Range("A1:A10")
   'loop code here
Next rCell

Of course, you don't know your range stops at row 10, so you use the .End property of the Range object:
Sheet1.Range("A1",Range("A1000").End(xlUp))
That will start at cell A1000 and work up until it finds the first used cell.