I'm writing my first macro, having no prior knowledge of VB...fun.
Basically, I need the macro to open an input text file of tabulate
data and create a chart with that data.

The problem is that the amount of input data will change frequently, s
the plotting range has to be dynamic. When I recorded the macro, i
inserted absolute R1C1 cell references for the range function. I nee
to replace those references (for example, R1C2:R4332C2) with dynami
references that will select the entire column.

I noticed that you could do this easily in the A1 reference style, b
simply using H:H or whatever the column name is. But I can't find an
information on doing it in the R1C1 style, and it seems I have to us
R1C1 when writing the macro.

Thanks in advance for any help you can offer

--
Message posted from http://www.ExcelForum.com

Sorry to bump, but I really need your help, and this seems like probabl
an easy problem to solve compared to some of the other questions I'v
seen

--
Message posted from http://www.ExcelForum.com

You could refer to the column like:

activesheet.columns(8)...
instead of
activesheet.range("h:H")...

You can find the last used row of a column with something like:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"B").end(xlup).row
'or
lastrow = .cells(.rows.count,2).end(xlup).row
'.cells is very forgiving.
end with

Then you could use:

dim myRng as range
set myrng = .range(.cells(1,2),.cells(lastrow,2))
....

Then later, you can use:

...., myrng.address(external:=true), ...

There aren't too many (any???) things that have to use R1C1 reference style.

If this didn't help, you may want to post more details.




"Jonnyboy117 <" wrote:

I'm writing my first macro, having no prior knowledge of VB...fun.
Basically, I need the macro to open an input text file of tabulated
data and create a chart with that data.

The problem is that the amount of input data will change frequently, so
the plotting range has to be dynamic. When I recorded the macro, it
inserted absolute R1C1 cell references for the range function. I need
to replace those references (for example, R1C2:R4332C2) with dynamic
references that will select the entire column.

I noticed that you could do this easily in the A1 reference style, by
simply using H:H or whatever the column name is. But I can't find any
information on doing it in the R1C1 style, and it seems I have to use
R1C1 when writing the macro.

Thanks in advance for any help you can offer!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Thanks Dave, I ended up figuring it out before I saw your post.

The way I did it was to Dim a range, then Set that range equal to a
entire column using A1 references, like this:

Set TimeRange = Range("B:B")

Then when the SeriesCollection is set for the chart, I replaced th
R1C1 reference that had been recorded for the macro. It looked like:

SeriesCollection.XValues = (=rangedata!R1C2:R1C4332)

I changed that to simply:

SeriesCollection.XValues = TimeRange

Basically, I just didn't understand enough about how ranges work an
how the series collection was being defined. I got some help from th
Excel 2000 Bible in that regard, which I was able to search through fo
free at Amazon. I've ordered the book since it was so helpful.

Hopefully this info will help other beginners with similar problems

--
Message posted from http://www.ExcelForum.com