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]

hmmm... ok... how to do that before copying??

looks like very simple question but i m very new to this stuff...

 

[jindon]

Actually I think it is better to edit the original data.

How are you populating orginal data?

 

[ganesh_05]

the data comes from another source on which i dont have any hold... first the data comes to sheets("f_Output")...

either i can edit in f_Output sheet or in sheet1...

any idea?

 

[jindon]

run this code right after you import the data

Sub test()
With Sheets("f_Output")
    a = .UsedRange.Value
    For i = 1 To UBound(a, 1)
        For ii = 1 To UBound(a, 2)
            If Not IsEmpty(a(i, ii)) And Not IsDate(a(i, ii)) _
                And IsNumeric(a(i, ii)) Then
                a(i, ii) = Application.Round(a(i, ii), 2)
            End If
        Next
    Next
    .Range("a1").Resize(UBound(a, 1), UBound(a, 2)) = a
End With
Erase a
End Sub

 

[ganesh_05]

great.. working...

thank you...

 

[ganesh_05]

Hi jindon,

There is a new problem...

initially, it was only one combobox from which i have to select the product and display the data related to that product...

now i have two comboboxes... and i have to display the data that matches both comboboxes...

i m using the following code... but its showing the data incorrectly...

Function qtyproc(q As String, p As String)
'Procedure to fill the data for import-export details

Dim s As Range, r As Range
Dim NTP As Integer, k As Integer, colrow As Integer, i As Integer
Dim x As Integer, y As Integer, z As Integer, listrange As String

If q = "" Or p = "" Then Exit Function

With Sheets("Parameters")
    NTP = .Range("B11")
    
End With

Sheets("sheet1").Range("A2:AZ65536").ClearContents

With Sheets("qty_Output")

    k = 0
    k = Application.CountIf(.Range("A1", .Range("A65536").End(xlUp)), q)
    
    
    Set r = .Cells.Find(what:=q, LookIn:=xlFormulas, _
        lookat:=xlPart, searchOrder:=xlByRows, _
        searchdirection:=xlNext, MatchCase:=False)
    
    If r Is Nothing Then Exit Function
    
    If k = 0 Then
        MsgBox "There are no details of Product " & q
        Exit Function
    End If
    
    colrow = r.Row
        
    'Set s = .Cells.Find(what:=p, LookIn:=xlFormulas, _
    '    lookat:=xlPart, searchOrder:=xlByRows, _
    '    searchdirection:=xlNext, MatchCase:=False).Resize(colrow, k)
        
    i = Application.CountIf(.Range(.Cells(r.Row, 2), .Cells(r.Row + k, 2)), p)
    
    If i = 0 Then
        MsgBox "There are no details of the Product " & p
        Exit Function
    End If
    
    z = 0
    ReDim data(1 To k, 1 To NTP + 3)
    For x = 1 To k
      If Sheets("qty_Output").Cells(colrow + x - 1, 2) = p Then
        z = z + 1
        For y = 1 To (NTP + 3)
            data(z, y) = Cells(colrow + x - 1, y + 2).Value
        Next y
      End If
    Next x
        
End With
Sheets("sheet1").Cells(2, 1).Resize(UBound(data, 1), UBound(data, 2)) = data

With Sheets("sheet1").Range("a1").CurrentRegion

    .Range("A1") = "Imptd from Facility"
    .Range("B1") = "During Period"
    .Range("C1") = "To Facility"
    

End With

FrmQty.ListBox1.ColumnCount = (NTP + 3)
FrmQty.ListBox1.ColumnHeads = True
FrmQty.ListBox1.RowSource = "=sheet1!A2:AF" & (z + 1)

End Function

help needed...

thank you...

 

[ganesh_05]

i m triggering the above function from combobox change event (from both comboboxes change event)

 

[jindon]

ganesh_05,

Can function prcedure change the value of the listbox?

I've never done it.

Why don't you try to convert to it to Sub procedure?

 

[ganesh_05]

Hi Jindon,

because of this reason it is showing the data wrongly in the list box? the function is able to display the data in the listbox but incorrectly. Is there anything wrong in my code??

anyway, i will change it to sub from function.

Thankyou.

 

[ganesh_05]

Hi Jindon,

changed it to Sub. But the data it is showing not correct. actually it showing the same data for any pair of comboboxes text. here q and p are arguments and are comboboxes text.

so for any combination of p and q the data it is showing the same. i think there is some wrong in my code. please check it.

Thank you.