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.

 

[jindon]

You can only add column headers from the sheet

say, your range is B2:Z5, you can only place headers on row1
which is B1:Z1 with ColumnHeader property set True

If your range does not begin from row2, you need to create
working range somewhere else

 

[ganesh_05]

According to combox value, i have to display the data in a listbox with the same headers. suppose if combobox value is p1, then i have to display the range B2: Z5
if combobox value is p2, then the range is B6:Z10 and so on...

what i m doing now is...

i have created a new sheet with the headers in first row... and whenever combobox text changes i m copying the range from the main sheet to the new sheet in the range starting from B2. so that for every combobox value the range starts from B2 in the new sheet and the headers will be the same. I had changed the rowsource property of listbox to the new sheet.

but its taking lot of time to display the data... still i m trying for an efficient method... !!

btw, do u know how to draw/insert border lines between columns in listbox??

Thankyou.

Ganesh.

 

[jindon]

btw, do u know how to draw/insert border lines between columns in listbox??

I don't think you can.

but its taking lot of time to display the data

it shouldn't take long.
Do you want to show us your code?

 

[ganesh_05]

Ok here is my code:

Private Sub ComboBox1_Change()
Dim cell As Range, range1 As Range, range2 As Range
Dim listrange As String, NTP As Integer, k As Integer, ColRow As Integer

NTP = Sheets("Parameters").Range("B11")
k = 0

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
k = k + 1
End If
Next cell

End With

ActiveSheet.Range("A1").Select
Cells.Find(What:=ComboBox1.Text, After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate

ColRow = ActiveCell.Row

On Error Resume Next

ReDim data(1 To k, 1 To NTP + 2)
For i = 1 To k
For j = 1 To (NTP + 2)
data(i, j) = Cells(ColRow + i - 1, j + 1).Value
Next j
Next i

For i = 1 To k
For j = 1 To (NTP + 2)
Sheets("Sheet1").Cells(i + 1, j).Value = data(i, j)
Next j
Next i

ListBox1.ColumnCount = (NTP + 2)
ListBox1.ColumnHeads = True
ListBox1.RowSource = "=sheet1!A2:AB" & k


End Sub

 

[jindon]

hope this works

Private Sub ComboBox1_Change()
Dim cell As Range, range1 As Range, range2 As Range, r As Range
Dim listrange As String, NTP As Integer, k As Integer, ColRow As Integer

With Sheets("Parameters")
    NTP = .Range("B11")
    k = 0
    Set range1 = .Range("B1:B2")
End With
With Sheets("f_Output")
    k = Application.CountIf(.Range("A1", .Range("A65536").End(xlUp)), ComboBox1.Text)
    Set r = .Cells.Find(What:=ComboBox1.Text, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If r Is Nothing Then Exit Sub
    
    ColRow = r.Row

    On Error Resume Next
    
    ReDim data(1 To k, 1 To NTP + 2)
    For i = 1 To k
        For j = 1 To (NTP + 2)
            data(i, j) = Cells(ColRow + i - 1, j + 1).Value
        Next j
    Next i
End With

    Sheets("Sheet1").Cells(i + 1, j).Resize(UBound(data, 1), UBound(data, 2)) = data

ListBox1.ColumnCount = (NTP + 2)
ListBox1.ColumnHeads = True
ListBox1.RowSource = "=sheet1!A2:AB" & k
End Sub

 

[jindon]

or maybe

Private Sub ComboBox1_Change()
Dim cell As Range, range1 As Range, range2 As Range, r As Range
Dim listrange As String, NTP As Integer, k As Integer, ColRow As Integer

With Sheets("Parameters")
    NTP = .Range("B11")
    k = 0
    Set range1 = .Range("B1:B2")
End With
With Sheets("f_Output")
    k = Application.CountIf(.Range("A1", .Range("A65536").End(xlUp)), ComboBox1.Text)
    Set r = .Cells.Find(What:=ComboBox1.Text, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If r Is Nothing Then Exit Sub
    
    ColRow = r.Row
    .Cells(ColRow, 2).Resize(k, NTP + 2).Copy Sheets("sheet1").Range("a2")
End With
With Sheets("sheet1").Range("a1").CurrentRegion
    listrange = .Offset(1).Resize(.Rows.Count - 1).Address
End With
ListBox1.ColumnCount = (NTP + 2)
ListBox1.ColumnHeads = True
ListBox1.RowSource = "=sheet1" & listrange
End Sub

 

[jindon]

or maybe

Private Sub ComboBox1_Change()
Dim cell As Range, range1 As Range, range2 As Range, r As Range
Dim listrange As String, NTP As Integer, k As Integer, ColRow As Integer

With Sheets("Parameters")
    NTP = .Range("B11")
    k = 0
    Set range1 = .Range("B1:B2")
End With
With Sheets("f_Output")
    k = Application.CountIf(.Range("A1", .Range("A65536").End(xlUp)), ComboBox1.Text)
    Set r = .Cells.Find(What:=ComboBox1.Text, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If r Is Nothing Then Exit Sub
    
    ColRow = r.Row
    .Cells(ColRow, 2).Resize(k, NTP + 2).Copy Sheets("sheet1").Range("a2")
End With
With Sheets("sheet1").Range("a1").CurrentRegion
    listrange = .Offset(1).Resize(.Rows.Count - 1).Address
End With
ListBox1.ColumnCount = (NTP + 2)
ListBox1.ColumnHeads = True
ListBox1.RowSource = "=sheet1" & listrange
End Sub

 

[ganesh_05]

Ohh thats excellent...

thanks alot....

hav a great time...

cheers!
Ganesh.

 

[ganesh_05]

Hi jindon...

one small doubt...

how to round the data upto 2 decimal places while copying to new sheet "sheet1" ??

 

[jindon]

I don't think you can edit the data while it is in the clipboard.

You need to edit the data either before or after copy/paste