Specific datapoints selection

[Metin]

Hi all,

I have created a chart. Now I want to search for specific cell values in
Column A, and use the data which belongs to this selection in column C as x
value, and the data in column F as y value. With VBA.

Example:
A B C D E F
1 A23C R2 65 17 26 119
2 A23Q R2 44 25 24 128
3 WT R2 49 8 10 145
4 A23M R2 54 19 18 98
5 WT R2 61 7 11 149

In column A the sample ID is presented. I need to select the rows wich have
the text 'WT' in column A. From this selection I want to use the data in
column C as x value and the data in column F as y value (xValue = C3 and C5)
(yValue = F3 and F5).
I have made the next macro, but it's not working.

Sub Testi_1()
Set wks = Worksheets("Calculated Data")

v = 2
w = 5
For i = 1 to 3
exSh.Select
Set rng = Range("C2:S97")
xWT = Range(rng, "WT", rng.Offset(0, v))
yWT = Range(rng, "WT", rng.Offset(0, w))
ActiveChart.SeriesCollection(3).XValues = xWT
ActiveChart.SeriesCollection(3).Values = yWT
ActiveChart.SeriesCollection(3).Name = "=""ASP Reference samples"""

v = v + 1
w = w + 1
Next
End Sub

 

[Markus Scheible]

Hi Metin,

although I understood what you want to do, I do not
understand what your macro shall do - sorry...
nevertheless I found some errors:

Sub Testi_1()
Set wks = Worksheets("Calculated Data")

v = 2
w = 5
For i = 1 to 3
exSh.Select

What is exSh ? Is this object defined before? Otherwise
excel cannot select it.

Set rng = Range("C2:S97")
xWT = Range(rng, "WT", rng.Offset(0, v))
yWT = Range(rng, "WT", rng.Offset(0, w))
ActiveChart.SeriesCollection(3).XValues = xWT
ActiveChart.SeriesCollection(3).Values = yWT
ActiveChart.SeriesCollection(3).Name = "=""ASP Reference samples"""

v = v + 1
w = w + 1

Within your loop you haven't used i... so it is no real
loop because it just runs once.

Maybe you wanted to use SeriesCollection(i) instead? I
don't really know...

Next

This is no close statement for a for-loop. Next must
define which variable should be used... so use "Next i".

End Sub

Maybe that helps a little bit?

Best

Markus

 

[Metin]

1. 'ExSh' is wrong it has to be 'wks'. wks is defined.
2. The i was a mistype. Just forgot the i and the loop.
The error is in defining the x values and the y values.
xWT = Range(rng, "WT", rng.Offset(0, v)) is wrong. With this line I want to
select all data which is in the row where the 'WT' is found, but 'v' columns
further. In the next line I want to use the previous selection (with the WT)
for the x values of the chart.

P.S. Markus, do you now somebody in the Netherlands who I can hire in to
help me with this kind of macro's and other macro's for automatically
calculations in Excel?

 

[Markus Scheible]

Hi Metin,

-----Original Message-----
1. 'ExSh' is wrong it has to be 'wks'. wks is defined.
2. The i was a mistype. Just forgot the i and the loop.
The error is in defining the x values and the y values.
xWT = Range(rng, "WT", rng.Offset(0, v)) is wrong. With this line I want to
select all data which is in the row where the 'WT' is found, but 'v' columns
further. In the next line I want to use the previous selection (with the WT)
for the x values of the chart.

Well, okay. I would do it like the following (presumed
that you just search for an explicitly known entry within
column A such as "WT"):

i = 3
For Each cell In Range("A1", "A1000")
If cell.Value = "WT" Then
ActiveChart.SeriesCollection(i).XValues = Range("C" &
cell.Rows).Value
ActiveChart.SeriesCollection(i).Values = Range("F" &
cell.Rows).Value
ActiveChart.SeriesCollection(i).Name = "whatever"
i = i + 1
End If
Next cell

Maybe change it a little bit like you need it... don't
know if it works already the way you want.

BTW: I wouldn't work with the activechart statement... try
define this chart explicitly...

P.S. Markus, do you now somebody in the Netherlands who I can hire in to
help me with this kind of macro's and other macro's for automatically
calculations in Excel?

Sorry, don't know, I am from Germany... maybe you can
contact one of the Excel MVPs within this newsgroup....
often they work as professional programmers.

Best

Markus

 

[Jon Peltier]

You don't really need VBA.

Insert a row and give each column a header label in the first row.

On the Data menu, choose Filter > AutoFilter.

In the dropdown on cell A1, select WT, which hides all the non-WT lines.

Now select columns C and F and create an XY Scatter chart with the Chart
Wizard.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Metin]

Markus, thanks for your help. But when I run this macro I get an
errormessage: Run-time error '1004', Method 'Range' of object '_Global'
failed.
What this error message mean. What is going wrong.

 

[Jon Peltier]

I don't think Markus tested the code.

I have a few links in this old post that go through a range and remove
zeros from the range. You could adjust the criteria for admission into
the range so that rows with "WT" are included.

http://groups-beta.google.com/group/microsoft.public.excel.programming/msg/1d47f5969d7adb84

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Here's another non-VBA approach. In G1 put this formula:

=IF(A1="WT",F1,NA())

This puts F1's value into G1 if A1 has WT, or it puts #N/A. Copy this
formula down the column, then plot columns C and G. The rows with #N/A
do not appear in the chart. If you make a line chart, the line will pass
over the #N/A values, connecting the values on either side.

Debra Dalgleish shows how to use conditional formatting to hide the
error values in column G:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______