Resolved(Sorry Again Please Advise on this)

[VBcielle]

can you help me make a work around for this problem

i have two combo box in sheet 1
1. For Pal Line
2. For Transaction Level

all values in pal line and transaction values is generated in
Sheet 2 as my database

see attached screenshot of my sheets.

how can i fill the range of all PAL line Colums in my Pal Line combo bOx
as well as fill the range of the transaction within the Pal Line?

for Example PAl Line 1 has 2 transactions
and Pal Line 2 has 1 transaction

so if user's choose Pal Line 1 value in the PAL lIne combo box
only two transactions will appear in the Transaction Level combo box

help me..

[westconn1]

in the click or change event for pal line change the listfill range of transaction level to suit, or if you are not using a fill range add the appropiate items to to the combobox

[VBcielle]

thanks sorry for the late reply..how about for the transaction combo box?
i want to fill the Transaction base on the no. of transaction in the Pal line..

for ex. in the image above Pal line 1 have transaction 1,2,3
while Pal Line 2 have only transaction 1...any workaround?

[VBcielle]

Ok so to additem in Transaction comboBox i put this code in the change event of the Pal combo box
is this the right code?

LC = Sheet17.Cells.Find(what:="Transaction", after:=cmbValue, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Value

I want to find all transactions using combo value in the change event..

for example PAL line 1 has 3 transaction i only want to display Transaction 1, Transaction 2, Transaction 3..

Please help

[Hack]

I want to find all transactions using combo value in the change event..

Typically I use the click event of a control rather than its Change event.

[westconn1]

try like

Private Sub ComboBox1_Click()
dim c as range
ComboBox2.Clear
 
If ComboBox1.Text = "a" Then
    For Each c In Range("b1:d1")
    ComboBox2.AddItem c.Value
    Next
ElseIf ComboBox1.Text = "b" Then
    For Each c In Range("f1:g1")
    ComboBox2.AddItem c.value
    Next
End If
End Sub

where the range in each case contains the transactions fort the appropriate pal line
or you could find the pal line then loop through until you reach the next pal line

[VBcielle]

Thanks for the reply...Transactions in each Pal could be more than 3...you know it's changing... I want to know the end of range of Transactions..

[VBcielle]

Sub testPalLIne()
'JIL
Call TestFindAll("Pal Line 1")
End Sub

I have this code below find first the Pal Line then next transaction but I cannot terminate adding of transaction until Found cells for Pal Line is not equal to the combo value..please help..

Public Sub TestFindAll(cmbValue As Variant)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TestFindAll
' This is a test procedure for FindAll.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''    
    Dim SearchRange As Range
    Dim FoundCells As Range
    Dim FoundCell As Range
    Dim FindWhat As Variant
    Dim MatchCase As Boolean
    Dim LookIn As XlFindLookIn
    Dim LookAt As XlLookAt
    Dim SearchOrder As XlSearchOrder
    
    Dim SearchRange1 As Range
    Dim FoundCells1 As Range
    Dim FoundCell1 As Range
    Dim FindWhat1 As Variant
    Dim MatchCase1 As Boolean
    Dim LookIn1 As XlFindLookIn
    Dim LookAt1 As XlLookAt
    Dim SearchOrder1 As XlSearchOrder

    ''''''''''''''''''''''''''
    'Find First All Pal Line
    '''''''''''''''''''''''''''    

    Set SearchRange = Sheet17.Range("A4:IV4")
    FindWhat = "Pal"
    LookIn = xlValues
    LookAt = xlPart
    SearchOrder = xlByColumns
    MatchCase = False
    
    '''''''''''''''''''
    ' Search the range.
    '''''''''''''''''''    
Set FoundCells = FindAll(SearchRange:=SearchRange, FindWhat:=FindWhat, _
        LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
        
    ''''''''''''''''''''''''''
    'Second Find All Transaction
    '''''''''''''''''''''''''''    

   Set SearchRange1 = Sheet17.Range("A4:IV4")
       
    FindWhat1 = "Transaction"
    LookIn1 = xlValues
    LookAt1 = xlPart
    SearchOrder1 = xlByColumns
    MatchCase1 = False
    
    '''''''''''''''''''
    ' Search the range.
    '''''''''''''''''''    
Set FoundCells1 = FindAll2(SearchRange:=SearchRange1, FindWhat:=FindWhat1, _LookIn:=LookIn1, LookAt:=LookAt1, SearchOrder:=SearchOrder1, MatchCase:=MatchCase1)
    
    ''''''''''''''''''''''
    ' Display the results.
    '''''''''''''''''''''' 

    For Each FoundCell In FoundCells.Cells 'Find Pal lines
     If FoundCell.Text = cmbValue Then ' If Found cell equal to comboValue     
       For Each FoundCell1 In FoundCells1.Cells 'Find All Transactions        
    Do
         ActiveSheet.ComboBox2.AddItem FoundCell1.Text
        Loop Until (FoundCell <> cmbValue) 'Add Transaction Until Find Pal Lines not equal to cmbValue       
Next FoundCell1

    End If
   Next FoundCell

    
End Sub

[westconn1]

1.
      Private Sub ComboBox1_Click()
      dim c as range, fnd as boolean
      ComboBox2.Clear
       myrng = range("a1:a99")      ' row with pal lines and transactions, however many columns you want
         for each c in myrng
               if fnd then 
                       if left(c.value, 8)  = "pal line" then    ' found next pal line so finish
                            exit for
                       else combobox2.additem c.value
                       end if
               else
               if c.value = combobox1.text then fnd = true  ' find pal line cell to match combo 1
               end if
         next
 
       End Sub

i didn't test any of this, make sure next pal line matches for case etc e&oe

if you were searching the entire sheet, then i would use find to start, but as i believe you know which line the pal and transaction are in i just work with that

[VBcielle]

hello Thanks again for the help...i tested you code in a macro button and it appears like this

[VBcielle]

Left code is case sensitive i just change "pal line" to "Pal Line" thanks agaiin..this one is resoveld

[VBcielle]

can i ask a little more..how can i get the column count of the last transaction for example Pal Line 1 which is transaction 3 ?

[westconn1]

try this one

Private Sub ComboBox1_Click()
      dim c as range, fnd as boolean
      ComboBox2.Clear
       myrng = range("a1:a99")      ' row with pal lines and transactions, however many columns you want
         for each c in myrng
               if fnd then
                       if left(c.value, 8)  = "pal line" then    ' found next pal line so finish
                            msgbox "last transaction line for " & combobox1.text & " is in column " & c.column
                            exit for
                       else combobox2.additem c.value
                       end if
               else
               if c.value = combobox1.text then fnd = true  ' find pal line cell to match combo 1
               end if
         next
 
       End Sub

or
combobox2.listcount
depending what you want

[VBcielle]

thanks again for the help.