PDA

View Full Version : Define Column/Listbox



zuzzu
03-10-2011, 07:19 AM
Hello everyone,

I just registered myself to the forum and I hope i can find answers for my questions :)

I searched in forum but couldn't find exactly what I am looking for.

I add an excel file. On 9th line, I have very long codes for the experiments. They are 4 different experiments such as: T121509-03,
T121609-06, T121509-02 and T122109-02. (P1-5 characters are not important).

What I need is to pass these 4 main codes as TXXXXXX-XX to a listbox. But the codes are different in each experiment data excel file so they are static.

I dont know how I can force the program to look for only TXXXXXX-XX values and then put them in Listbox1 without repetition?

My second question is on 10th line, there are units. If i.e. the cell value is equal to "bar", then the whole column starting from 15th line should be accepted as pressure values. How can I define this cloumn?

Your helps would be appreciated, thank you very much in advance.

Cheers.

Tinbendr
03-10-2011, 08:34 AM
Welcome to VBA Express!

Private Sub UserForm_Initialize()
Dim lbTest As New Collection
Dim LastCol As Long
Dim aCol As Range
Dim TestNum As String
With ActiveSheet
LastCol = .Cells(9, .Columns.Count).End(xlToLeft).Column
For Each aCol In .Range(.Cells(9, 2), .Cells(9, LastCol))
TestNum = Trim(Mid(aCol, 4, 10))
If Left(TestNum, 1) = "T" Then
On Error Resume Next
lbTest.Add TestNum, TestNum
If Err = 0 Then
Me.ListBox1.AddItem TestNum
Else
Err = 0
End If
End If
On Error GoTo 0
Next
End With
End Sub

I don't understand your second question.

David

zuzzu
03-10-2011, 08:42 AM
Dear David,

Thank you very much for your quick reply.

My second question is: In the added file, on 10th row, there are units such as "bar", "µm/m", "°C" and "s". They define the value type of the datas starting from row 15.

So, if in cell E10, "°C" is written; the values starting from E15 to E(last row) should be accepted as temperature values.

--> My aim is to chose:
1. The experiment from code TXXXXXX-XX by Listbox
2. Get the temperature(°C), pressure(bar), strain(µm/m) and time(s) values of the wanted experiment.
3. Draw a line diagram: temp, pressure, strain versus time.

I am very new on VBA. I dont know if my program plan sounds applicable and logical?

I appreciate your helps, thank you very very much.

Tinbendr
03-10-2011, 02:07 PM
1. The experiment from code TXXXXXX-XX by Listbox
2. Get the temperature(°C), pressure(bar), strain(µm/m) and time(s) values of the wanted experiment.
I'm not understanding the corelation between the experiment and the data. Is the data under the experiment number the data you're after? If so, then some have 10-15, while others only have a couple.

Record a macro creating the graph and show it to us.

zuzzu
03-11-2011, 05:15 AM
Hi David,

Yes the data that I am going to use start from Row 15 down to the last row (I wrote as example the values as: 1,2,3,4,5,6,7,.......................to end row).

I add the example file with explanations. I know it is very very complicated.

If i explain what I want again:
1. User selects the excel file for datas
2. In userform, in listbox1, the codes of the experiments will ve listed.
3. After choosing the experiment code from Listbox1, The columns that have the temperature, bar, strain and time datas will be chosen and a graph will be drawn.

* Some experiments have extra Ros1,2,3 values, they will be also chosen in an other listbox2 after listbox1.

I also recorded a macro showing how to draw a plot with experiments having Ros extra code.

For P5 T122109-02 Ros1a,b,c: Strain
For P5 T122109-02 Temp Ros1: Temperature
For P5 T122109-02 PN110: Pressure

I really appreciate very much your helps.

Thank you very much.

zuzzu
03-14-2011, 12:26 AM
Hello,

I would appreciate your helps. I can not go any further because of this defining problem.

Thank you very much.