Quick Column Related Question...

[Holte]

This may be trivial to some of you but, here goes...

I have a worksheet which comprises of a sheet of a lot of raw data from which I am trying to automate the creation of some charts. However, the amount of rows and columns can vary so here is my dilemma.

The charts always use the A column as the X-Axis but, the Y-Axis comprises of two adjacent columns which move along from chart to chart...e.g. B & C for the first one, D & E for the next and so on. I was thinking of writing a while loop searching for an empty column as it moves along (thus indicating the end) however, it is this moving along the columns which is causing the headache. The code I have so far is a little basic but, here is a cut and paste:

============================================
Sub Chart()

Dim ChartNo As Byte
Dim FullCellRef As String
Dim CellRef As String
Dim SheetName As String
Dim Fruni As String
Dim CurrentCell As String
Dim ColumnNo As String
Dim ChartRange As Integer

' Defining a variable that increments to allow sheet renaming.
ChartNo = 1
' Trying to define a variable to use to move the column along.
ColumnNo = "A:A"

While ActiveCell.Value <> ""


Charts.Add
ActiveChart.ChartType = xlLine

' This is the line where I am having the problem, how can I replace the column letters with a variable that can be incremented to allow moving along two columns at a time..?? Remember that "A:A" is always the X-Axis.

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range( _
"A:A, B:B, C:C"), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("C1").Activate

FullCellRef = Left(ActiveCell.Value, 13)
Fruni = Right(Right(FullCellRef, 3), 2)
CellRef = Left(FullCellRef, 10) & Fruni
Sheets("Chart" & ChartNo).Name = CellRef
ActiveCell.Offset(0, 2).Select

ChartNo = ChartNo + 1
ColumnNo = ColumnNo + 2

Wend

End Sub
============================================

Thanks in advance for any help given.

 

[Jim North]

Have you tried:

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range( _
"A:A, B:C"), PlotBy:=xlColumns

??

 

[Holte]

That would chart both columns but, I need a way to increment that every time through the loop. e.g. "B:C" then becomes "D:E" and "F:G" and so on. It doesn't have to be done with letters if it can be done more easily using cell index references or some other such numeric expression. My thoughts were that it would be easy if the table only went from column A to Z but, how does Z+1 become AA in that formula..?? I agree that it could be done but, the coding seems a little unnecessary if it can be done more easily with numbers.

Thanks in advance,


Mark

 

[Jim North]

Check out the CELLS property. I had to key in the word "cell" for it to come up in help:<pre>
Cells Property Example

This example sets the font size for cell C5 on Sheet1 to 14 points.

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14
This example clears the formula in cell one on Sheet1.

Worksheets("Sheet1").Cells(1).ClearContents
This example sets the font and font size for every cell on Sheet1 to 8-point Arial.

With Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With
This example loops through cells A1:J4 on Sheet1.
If a cell contains a value less than 0.001, the example replaces that value with 0 (zero).

For rwIndex = 1 to 4
For colIndex = 1 to 10
With Worksheets("Sheet1").Cells(rwIndex, colIndex)
If .Value< .001 Then .Value = 0
End With
Next colIndex
Next rwIndex
This example sets the font style for cells
A1:C5 on Sheet1 to italic.

Worksheets("Sheet1").Activate
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
This example scans a column of data named "myRange." If a cell has the same
value as the cell immediately above it, the example displays the address of the cell
that contains the duplicate data.

Set r = Range("myRange")
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = r.Cells(n + 1, 1) Then
MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address
End If
Next n</pre>

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-08-22 10:51

 

[Holte]

Hi Jim,

Have come up with method to workout how many rows and columns have data in and have stored this to a variable, so don't necessarily need to refer to a whole column any more. However, I still have a very frustating problem. In the following pieces of code, I cannot find a way to refer to a variable in the chart range specification. Any ideas..??

I have tried various methods:

A) By defining a union that is equal to the chart area:

ChartArea = Application.Union(Columns(1), Columns(ColumnNo), Columns(ColumnNo + 1))
ActiveChart.SetSourceData Source:=Sheets("sheet1").Range(ChartArea), PlotBy:=xlColumns

(ColumnNo is a variable containing the current column number)

B) By trying to refer to it using Cells:

I would cut and paste code into here but, I couldn't even find a combination of syntax that would actually compile.

C) By trying to use the Columns command:

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range(Columns(1), Columns(ColumnNo), Columns(ColumnNo + 1)), PlotBy:=xlColumns

(As I stated before, Column 1 is always the X axis)

None of these work even though a couple of them will actually compile. I have done MsgBoxs to prove that the values being passed are correct but, still no joy.

Again, thanks in advance


Mark

 

[Holte]

*bump*