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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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..
Code:
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
Last edited by VBcielle; Jan 13th, 2009 at 09:38 PM.
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
Last edited by westconn1; Jan 14th, 2009 at 01:46 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Resolved(Sorry Again Please Advise on this)
Reply With Quote
Originally Posted by VBcielle
