Snasui Excel Forum

Code: Select all

Public Sub search_quotation()

Dim quotation_search As String
Dim search_row As Single
Dim quotation_no As String

Dim i As Integer
Dim c As Integer

Const quotation_sheet = "Quotation"
Const quotation_db_sheet = "db_quotation"

Const q_no_col = 1
Const q_date_col = 2

Const q_customercode_col = 3
Const q_contactperson_col = 4
Const q_customeraddr_col = 5

Dim q_item_details_col(1 To 10) As Byte
Dim q_item_amount_col(1 To 10) As Byte
Dim q_item_price_col(1 To 10) As Byte

Const item_details_range = "c"
Const item_amount_range = "d"
Const item_price_range = "e"

'************************************************************************************************

quotation_search = Worksheets(quotation_sheet).quotation_no_txtbox.Value

search_row = 2
quotation_no = Worksheets(quotation_db_sheet).Cells(search_row, q_no_col).Value

    Do Until quotation_no = quotation_search
    
        search_row = search_row + 1
        quotation_no = Worksheets(quotation_db_sheet).Cells(search_row, q_no_col).Value
        
    Loop
    
With Worksheets(quotation_sheet)
    
    .Range("f8").Value = Worksheets(quotation_db_sheet).Cells(search_row, q_no_col).Value
    .Range("f9").Value = Worksheets(quotation_db_sheet).Cells(search_row, q_date_col).Value
    .Range("f10").Value = Worksheets(quotation_db_sheet).Cells(search_row, q_customercode_col).Value
    
    .Range("c12").Value = Worksheets(quotation_db_sheet).Cells(search_row, q_contactperson_col).Value
    .Range("c13").Value = Worksheets(quotation_db_sheet).Cells(search_row, q_customeraddr_col).Value
    
     
    q_item_details_col(1) = 6
    q_item_amount_col(1) = 7
    q_item_price_col(1) = 8
    
    i = 1
    For i = 2 To 10
        
        q_item_details_col(i) = q_item_details_col(i - 1) + 3
        q_item_amount_col(i) = q_item_amount_col(i - 1) + 3
        q_item_price_col(i) = q_item_price_col(i - 1) + 3
    
    Next i
     
     
     i = 1
     c = 1
     For i = 17 To 26
    
        .Range(item_details_range & i).Value = Worksheets(quotation_db_sheet).Cells(search_row, q_item_details_col(c)).Value
        .Range(item_amount_range & i).Value = Worksheets(quotation_db_sheet).Cells(search_row, q_item_amount_col(c)).Value
        .Range(item_price_range & i).Value = Worksheets(quotation_db_sheet).Cells(search_row, q_item_price_col(c)).Value
        
         c = c + 1
    
     Next i     

End With

End Sub

Public Sub search_invoice()

Dim invoice_search As String
Dim search_row As Single
Dim invoice_no As String

Dim i As Integer
Dim c As Integer

Const invoice_sheet = "invoice"
Const invoice_db_sheet = "db_invoice"

Const i_no_col = 1
Const i_date_col = 2

Const i_customercode_col = 3
Const i_contactperson_col = 4
Const i_customeraddr_col = 5

Dim i_item_details_col(1 To 10) As Byte
Dim i_item_amount_col(1 To 10) As Byte
Dim i_item_price_col(1 To 10) As Byte

Const item_details_range = "c"
Const item_amount_range = "d"
Const item_price_range = "e"

'************************************************************************************************
invoice_search = Worksheets(invoice_sheet).invoice_no_txtbox.Value

search_row = 2
invoice_no = Worksheets(invoice_db_sheet).Cells(search_row, i_no_col).Value

    Do Until invoice_no = invoice_search
    
        search_row = search_row + 1
        invoice_no = Worksheets(invoice_db_sheet).Cells(search_row, i_no_col).Value
        
    Loop
    
With Worksheets(invoice_sheet)
    
    .Range("f8").Value = Worksheets(invoice_db_sheet).Cells(search_row, i_no_col).Value
    .Range("f9").Value = Worksheets(invoice_db_sheet).Cells(search_row, i_date_col).Value
    .Range("f10").Value = Worksheets(invoice_db_sheet).Cells(search_row, i_customercode_col).Value
    
    .Range("c12").Value = Worksheets(invoice_db_sheet).Cells(search_row, i_contactperson_col).Value
    .Range("c13").Value = Worksheets(invoice_db_sheet).Cells(search_row, i_customeraddr_col).Value   
     
    i_item_details_col(1) = 6
    i_item_amount_col(1) = 7
    i_item_price_col(1) = 8
    
    i = 1
    For i = 2 To 10
        
        i_item_details_col(i) = i_item_details_col(i - 1) + 3
        i_item_amount_col(i) = i_item_amount_col(i - 1) + 3
        i_item_price_col(i) = i_item_price_col(i - 1) + 3
    
    Next i
     
     
     i = 1
     c = 1
     For i = 17 To 26
    
        .Range(item_details_range & i).Value = Worksheets(invoice_db_sheet).Cells(search_row, i_item_details_col(c)).Value
        .Range(item_amount_range & i).Value = Worksheets(invoice_db_sheet).Cells(search_row, i_item_amount_col(c)).Value
        .Range(item_price_range & i).Value = Worksheets(invoice_db_sheet).Cells(search_row, i_item_price_col(c)).Value
        
         c = c + 1
    
     Next i

End With

End Sub

Code: Select all

'Other code
Dim cbObj As Object

Set cbObj = Worksheets(invoice_sheet).Shapes("Drop Down 6")
invoice_search = cbObj.ControlFormat.List(cbObj.ControlFormat.Value)
'Other code

Code: Select all

Private Sub Workbook_Open()
Dim NowRow As Long
NowRow = 2
Sheets("search").ComboBox1.Clear
Do While Sheets("db_service").Range("A" & NowRow) <> ""
    Sheets("search").ComboBox1.AddItem Sheets("db_service").Range("A" & NowRow)
    NowRow = NowRow + 1
Loop
End Sub