+ Reply to Thread
Results 1 to 8 of 8

Specific datapoints selection

  1. #1
    Metin
    Guest

    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


  2. #2
    Markus Scheible
    Guest

    Specific datapoints selection

    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



  3. #3
    Metin
    Guest

    RE: Specific datapoints selection

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


  4. #4
    Markus Scheible
    Guest

    RE: Specific datapoints selection

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

    >.
    >


  5. #5
    Jon Peltier
    Guest

    Re: Specific datapoints selection

    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
    >


  6. #6
    Metin
    Guest

    RE: Specific datapoints selection

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

    > >.
    > >

    >


  7. #7
    Jon Peltier
    Guest

    Re: Specific datapoints selection

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

    >>


  8. #8
    Jon Peltier
    Guest

    Re: Specific datapoints selection

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1