Thread: Specific datapoints selection

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

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

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" wrote:

> 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
>
>
>

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

>"Markus Scheible" wrote:
>
>> 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
>>
>>
>>
>.
>

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 wrote:

> 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, 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.

"Markus Scheible" wrote:

> 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
>
> >"Markus Scheible" wrote:
> >
> >> 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
> >>
> >>
> >>
> >.
> >
>

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/...47f5969d7adb84

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

Metin wrote:

> 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.
>
> "Markus Scheible" wrote:
>
>
>>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
>>
>>
>>>"Markus Scheible" wrote:
>>>
>>>
>>>>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
>>>>
>>>>
>>>>
>>>
>>>.
>>>
>>

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/
_______

Jon Peltier wrote:

> 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 wrote:
>
>> 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
>>