| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number of records by Month that meet a specific requirement | Excel Worksheet Functions | |||
| How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) | |||
| How do I insert an image into a specific cell within a protected . | Excel Discussion (Misc queries) | |||
| SELECTION() | Excel Discussion (Misc queries) | |||
| Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel | |||
Linear Mode
