Select Cells, and performing macro on selected cells?

**Alfie [UK]** · Nov 5 '08, 09:55 AM

Re: Select Cells, and performing macro on selected cells?

On Wed, 05 Nov 2008 00:18:29 GMT, "Stratocast er" <stotz1@verizon .net>
wrote:

>Hello, and thank you for any help in advance.
>
>I need help determining if any commands exist in VB (Excel macro style) that
>can enable a user to select cells and run a macro which performs operations
>on those cells. I am a beginner and am not sure if this is possible.
>
>And if you'd really like to teach me a lesson. . . .
>
>Could this be iterated with a while loop?
>Suppose I wanted to simply make a graph of each row in a dataset and each
>graph also contains the last line in the set (so two rows being graphed, one
>fixed and one iterated). Assuming I had 10 rows (labels in Row 1 and Column
>1) I would want a seperate graph for each of the following pairs of rows:
>{(2,10), (3,10), . . . (9,10)}. Is this possible? If so, would you offer
>me any wisdom on how I could set this up?
>
>Until this point, record macro has been my only reliable source of
>information since I do not seem capable of looking up the magical command
>statements in the help menu to enable these actions. Again thank you for
>any help or advice.
>

Whilst VBA (Visual Basic for Applications) shares a lot of syntax with
VB Classic, it also has some differences :)

Record Macro is your best friend when first learning VBA, record a
task, then look at the code generated, and abstract it to a general
case that you can use.

Read the VBA help files as well, from within code view you can
highlight a command then press F1 to get taken to that section of help
directly, or start with the 'MS Excel VB Reference->Programming
Concepts' section which leads you through how to select and activate
ranges, etc.

You'll want to read up on; Range (object), Selection (property), and
Offset (property).

Also you might want to find a gentle starter tutorial to help you
understand the basic object model, syntax, working with collections,
etc, such as http://www.anthony-vba.kefra.com/index_011.htm or

http://pubs.logicalexpressions.com/Pub0009/LPMFrame.asp?CMD=ArticleSearch&AUTH=33

--
Alfie [UK]
<http://www.delphia.co. uk/>
What happens if you install windows 98 on a system with 2 processors? It crashes twice.

**Alfie [UK]** · Nov 20 '08, 10:05 AM

Re: Select Cells, and performing macro on selected cells?

On Fri, 14 Nov 2008 03:33:35 GMT, "Stratocast er" <stotz1@verizon .net>
wrote:

>Thanks for your input Alfie.

You're welcome :)

>However, this also forces Excel to graph all empty columns and the result is
>ugly. So then, how could I perform this graphing loop so that future
>updates would be recognized by these graphs?

There are 2 things you need to do, firstly work out what range you
need, then assign that into a data series.

To work out how far a row of data extends you can use a loop to scan
for empty cells, although this will depend upon how your data is laid
out. For instance;

Dim cnt As Long
cnt = 0
Do While Range("A1").Off set(1, cnt) <""
cnt = cnt + 1
Loop

....will step through the second row looking for an empty cell, cnt
will then hold the (R1C1 format) column offset of the first empty
cell.

ActiveSheet.Cha rtObjects("Char t 1").Activate
ActiveChart.Cha rtArea.Select
ActiveChart.Ser iesCollection.N ewSeries.Values = "=R2C1:R2C" & cnt

....will then add that series to the chart (you may get anomalous
results if that series is already included in the chart).

These are rather rough and ready solutions though, it's been a while
since I had to use Excel in any real capacity, and may not fit your
data series, but might give you another angle to approach it from.
--
Alfie [UK]
<http://www.delphia.co. uk/>
Some people drink from the fountain of knowledge...I think you barely gargled.

Select Cells, and performing macro on selected cells?

Select Cells, and performing macro on selected cells?

Comment

Comment