plss help...

[ganesh_05]

hii frnds,

I am a facing a problem with data handling in worksheets. I have some data like this...

In column A1, i have different variables like p1,p1,p1,p1,p1,p1,p2,p2,p2,p3,p3,p3,p4,p4,etc.

and the data related to p1(in cell A1) is from B1 to Z1, p1(cell A2) is from B2 to Z2, p1(cell A3) is from B3 to Z3 etc.

so i want to show all the data related to P1 (in all the rows) in a listbox using rowsource property. And the same i want to do it for p2, p3 etc.

how to do this??

Thanks in advance...

Ganesh.

 

[ganesh_05]

I don't know how many rows p1 occupies... how many rows p2 occupies... so cannot fix the rowsource property in that sense...

 

[ganesh_05]

what about the following code??

Set range1 = Range("B1:B2")

Sheets("f_Output").Activate
With ActiveSheet

For Each cell In .Range(Range("A1"), Range("A65536").End(xlUp))
If cell.Value = ComboBox1.Text Then
Set range2 = Range(cell.Offset(0, 1), cell.Offset(0, 50))
Set range1 = Union(range1, range2)
End If
Next cell

End With

'listrange = range1

ListBox1.RowSource = range1

 

[jindon]

If you have p1,p1,p1 in range("a1:a3")

how do you want the rowsource?

 

[ganesh_05]

then the rowsource should be B1:Z3

but i don't know whether P1 will be in 3 rows or 4 rows or 5 rows... its unknown parameter... i mean rownumber is a variable...

and is it possible to make column number also variable?? i mean wht if its not Zth column.. may be some other column that is not known a priori...

 

[jindon]

assuming the listbox on the sheet

Private Sub ComboBox1_Change()
Dim r1 As Range, r2 As Range, txt As String
With Me.ComboBox1
    Set r1 = Columns(1).Find(.Text, Cells(Rows.Count, 1), , xlWhole, , xlNext)
    If r1 Is Nothing Then Exit Sub
    Set r2 = Columns(1).Find(.Text, Cells(1, 1), , xlWhole, , xlPrevious)
    If r2 Is Nothing Then Exit Sub
    txt = Range("b" & r1.Row).Resize(r2.Row - r1.Row + 1, 25).Address
End With
Me.ListBox1.ListFillRange = txt
End Sub

 

[ganesh_05]

hii jindon,

its saying syntax error at...

Set r1 = Columns(1).Find(.Text, Cells(Rows.Count, 1), , xlWhole, , xlNext)
If r1 Is Nothing Then Exit Sub
Set r2 = Columns(1).Find(.Text, Cells(1, 1), , xlWhole, , xlPrevious)
If r2 Is Nothing Then Exit Sub
txt = Range("b" & r1.Row).Resize(r2.Row - r1.Row + 1, 25).Address

what mite be the reason ?

 

[jindon]

Are those controls on the sheet or userform?

I need to go out now, so the reply will be tomorrow.

 

[ganesh_05]

the controls are on userform...

ok byee...

 

[ganesh_05]

hi

cud solve my problem... but now a new problem...

whtever range i specify to my listbox to popup... i want the same headers for every range it displays... how to fix the headers??

i want the following series in column headers...

T1 T2 T3 T4 T5 T6 etc....

how to do this??