MsOf07 Stock control working with vb code (almost done just a few tweaks)-VBForums
Results 1 to 36 of 36

Thread: Stock control working with vb code (almost done just a few tweaks)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Stock control working with vb code (almost done just a few tweaks)

    hi every one

    need some help.

    Just need to fine tune the code so it can do the follwing:

    1║ add to the code and sheets the field "armazem = wherehouse", so that appears in all sheets and search results in first page

    2║ permit the code to search for only one item if needed and not having to select all the time the dropbox in first row and second

    3║ make the stock buttons work

    and the project is finished


    thanks

    Rsilva
    Attached Files Attached Files

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi


    anyone could give a look please,

    just need a small code correction

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    as i can not open .xlsm files i can not view your code
    i you saveAs .xls and reattach then maybe i can have a look
    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

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn

    i don't know whats wrong, but i can not upload the file nor xls, xlsx

    send me an e-mail in PM and i send the file to you

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    here's the file

    thank┤s

    Rsilva
    Attached Files Attached Files

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i have a look at your file, but would need more detailed information as to what you want to do, and what result you want
    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

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn

    what i need is simple as this i enter items at the top (searchboxes) and i get the results at the bottom (search results)

    Now when i press the search button i must have for exemple the color red written in the corresponding field and what it shoud do is get all items from all wherehouses and types but with the red colour

    hope you understand

    i will make a print with example.

    thanks

    rsilva

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    Name:  todo.jpg
Views: 327
Size:  481.8 KB

    hope you understand

    thanks

    rsilva
    Last edited by ricardo_mcs; Nov 15th, 2012 at 05:06 AM.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i did a little bit that may help you get started

    Code:
    Dim linhas As Integer
    Private Sub ComboBox1_DropButtonClick()
    Dim sh As Worksheet
    If ComboBox1.ListCount = 0 Then
        For Each sh In Sheets
            If Left(sh.Name, 3) = "ARM" Then ComboBox1.AddItem sh.Name
        Next
    End If
    End Sub
    
    
    Private Sub CommandButton1_Click()  'Search Part Command Button
    Dim x As String
    Dim lc As Long
    Dim fc As Range
    Dim y As Long
    Dim i As Long
    Dim op As Integer
    Dim tipo As String
    Dim refprod As String
    Dim prod As String
    Dim cod As String
    Dim acab As String
    Dim cor As String
    Dim lote As String
    Dim loc As String
    Dim forn As String
    
    op = 0
    
    If Range("B2").Value <> vbNullString Then   'if the value in cell B2 is not empty then
        x = Range("B2").Value   'set x equal to the value in B2
        y = 1   'y equal to 1 to reference the column B
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("C2").Value <> vbNullString Then   'if the value in B5 is nothing then if the value in C2 is not nothing then
        x = Range("C2").Value   'set x to the value in c2
        y = 2   'set y equal to 1 to reference column C
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("D2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("D2").Value   'set x to the vlaue in D2
        y = 3   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("E2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("E2").Value   'set x to the vlaue in D2
        y = 4   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("F2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("F2").Value   'set x to the vlaue in D2
        y = 5   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("G2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("G2").Value   'set x to the vlaue in D2
        y = 6   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("H2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("H2").Value   'set x to the vlaue in D2
        y = 7   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("I2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("I2").Value   'set x to the vlaue in D2
        y = 8   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("J2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("J2").Value   'set x to the vlaue in D2
        y = 9   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    Else
        y = 1
        x = "nada"
    End If  'end if statement
    
    
    If tipo = vbNullString Then
        tipo = "*"
    End If
    
    If refprod = vbNullString Then
        refprod = "*"
    End If
    
    If cod = vbNullString Then
        cod = "*"
    End If
    
    If acab = vbNullString Then
        acab = "*"
    End If
    
    If cor = vbNullString Then
        cor = "*"
    End If
    
    If lote = vbNullString Then
        lote = "*"
    End If
    
    If loc = vbNullString Then
        loc = "*"
    End If
    
    If forn = vbNullString Then
        forn = "*"
    End If
    
    If prod = vbNullString Then
        prod = "*"
    End If
    
    
    If Range("U5").Value <> vbNullString Then
        linhas = Range("U5").Value
        Else: Range("U5").Value = 15
    End If
    
    If linhas < 15 Then
            linhas = 15
            Range("U5").Value = 15
    End If
    Dim sh As Worksheet
    If ComboBox1.Text = "" Then
        For Each sh In Sheets
            If Left(sh.Name, 3) = "ARM" Then insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
        Next
    Else: Set sh = Sheets(ComboBox1.Text)
                insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
    End If
    
    
    
    End Sub
    Sub insertsearch(sh As Worksheet, y As Long, x As String, tipo As String, refprod As String, prod As String, cod As String, acab As String, cor As String, lote As String, loc As String, forn As String)
    Dim fc As Range, lc As Long, op As Long, i As Long
    With sh 'with the worksheet whose name is equal to the value selected in the machine combobox in cell a5
        'if the number of times that the value x (defined above based on cell filled in)
        'is found in the column y (defined above) more than 0 times then
        If WorksheetFunction.CountIf(.Columns(y), x) > 0 Then
            Set fc = .Cells(2, y)   'set fc equal to cell in row 2, column y(defined above)
                Range("U7").Value = WorksheetFunction.CountIf(.Columns(y), x)
                'loop through LC from one to the number of time x (defined above) appears in column y(defined above)
                For lc = 1 To WorksheetFunction.CountIf(.Columns(y), x)
                
                    'reset fc to the found cell in column y (defined above) whose value is equal to x (defined above)
                    Set fc = .Columns(y).Find(what:=x, After:=fc, _
                                                    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False)
                                                   
                        'when a value is found provide a message box listing the record found to make sure it is the correct record
                        If UCase(fc(1, 1)) Like UCase(tipo) And UCase(fc(1, 2)) Like UCase(refprod) And UCase(fc(1, 3)) Like UCase(prod) And UCase(fc(1, 4)) Like UCase(cod) And UCase(fc(1, 5)) Like UCase(acab) And UCase(fc(1, 6)) Like UCase(cor) And UCase(fc(1, 7)) Like UCase(lote) And UCase(fc(1, 8)) Like UCase(loc) And UCase(fc(1, 9)) Like UCase(forn) Then
                            If op = 0 Then
                                If MsgBox("╔ este o valor correcto?" & vbNewLine & _
                                    "Tipo de Produto: " & .Cells(fc.Row, 1) & vbNewLine & "Referencia Produto: " & .Cells(fc.Row, 2) & vbNewLine & _
                                    "Produto: " & .Cells(fc.Row, 3) & vbNewLine & "Codigo: " & .Cells(fc.Row, 4) & vbNewLine & _
                                    "Acabamento: " & .Cells(fc.Row, 5), vbYesNo) = vbYes Then
                                        For i = 1 To 18  'loop through the variable i from numbers 1 To 18 (represents column numbers)
                                            'distribute the found information accordingly to row 2 of the Stock worksheet
                                            Cells(2, i + 1) = .Cells(fc.Row, i)
                                        Next i  'move to next i in the i loop
                                        op = 1
                                End If
                            End If
                       
                            For i = 1 To 18 'loop through the variable i from numbers 1 To 18 (represents column numbers)
                                'distribute the found information accordingly to row 2 of the Stock worksheet
                                Cells(linhas, i + 1) = .Cells(fc.Row, i)
                            Next i  'move to next i in the i loop
    
                            linhas = linhas + 1
                        
                        End If
                        
                Next lc 'move to next lc in the loop
                Range("U5").Value = linhas
        ElseIf x = "nada" Then
                MsgBox "Preencha alguns campos para iniciar pesquisa", vbOKOnly, "Nada"  'if no value is found the msgbox part not available
            Else
            MsgBox "Part Not Available", vbOKOnly, "Not Found"  'if no value is found the msgbox part not available
        End If
    
    End With
    
    End Sub
    i removed the text from the combobox1 and populated it when required, i also split off some of the code to a separate procedure, but so far it is not returning the values correctly, which is caused by the line comparing the values
    Code:
     If UCase(fc(1, 1)) Like UCase(tipo) And UCase(fc(1, 2)) Like UCase(refprod) And UCase(fc(1, 3)) Like UCase(prod) And UCase(fc(1, 4)) Like UCase(cod) And UCase(fc(1, 5)) Like UCase(acab) And UCase(fc(1, 6)) Like UCase(cor) And UCase(fc(1, 7)) Like UCase(lote) And UCase(fc(1, 8)) Like UCase(loc) And UCase(fc(1, 9)) Like UCase(forn) Then
    you will have to compare that all the values return true
    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

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn

    The values are being returned, i also added in the bottom of each wherehouse a stock item called "test" to see if it returned ok and it does, but it stops on "Arm3"

    for now it is starting to make sence.

    i uploud the file so you can see it

    thanks

    rsilva
    Attached Files Attached Files

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    but it stops on "Arm3"
    i only did the sheet starting ARM, as i can not understand your language, if all the sheets are warehouse except search then change to

    Code:
        For Each sh In Sheets
            If not Left(sh.Name, 3) = "SEARCH" Then ComboBox1.AddItem sh.Name
        Next
    and similarly in the search proceedure
    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

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn

    i upload the file with the translation so that you understand

    in each sheet the meanings are:

    the arm1,2,3, you allready now,

    the others are: "GAP" prodution help departmant
    "produšao" production
    "parque" park
    "escritorio" office

    thanks for your time again
    Attached Files Attached Files

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    the others are: "GAP" prodution help departmant
    "produšao" production
    "parque" park
    "escritorio" office
    the code i posted in #11 should search all sheets, except search sheet
    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    Hi westconn,

    the page is starting to work, but it still wont get the values in the others sheets (yes all sheets are wherehouses exept search).

    the last code you written i've add it in the top of the code i thinkd that was what you mean.

    My VB code programing is very bad sorry for the inconvenient.

    i've uplouded the update sheet, if could please give a look i would be thankfull

    if you could i need that the wherehouses names apear in the search results in the wherehouse columm when a search is made


    thanks in advance

    rsilva
    Attached Files Attached Files

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i've add it in the top of the code
    you also need to modify the sheet loop in the search code similarly (near the bottom of command1 click procedure)

    search results in the wherehouse columm when a search is made
    add

    Code:
    cells(linhas, 1).value = sh.name
    before
    For i = 1 To 18 'loop through the variable i from numbers 1 To 18 (represents column numbers)
    'distribute the found information accordingly to row 2 of the Stock worksheet
    Cells(linhas, i + 1) = .Cells(fc.Row, i)
    Next i 'move to next i in the i loop
    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

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn,

    i have made the changes you told me but it still stop's at arm3 it wont continue to push the rest of the stock.

    I've added in all wherehouse the stock item called "test" to see how it works, works fine. But if i try to search for a item allready in stock for exemple "impala" it gives me the result part no avaiable for it to work i need to select type of product "second row" before hitting search

    down is my entire code please see where is the error

    tanks in advance

    rsilva


    Code:
    Dim linhas As Integer
    Private Sub ComboBox1_DropButtonClick()
    Dim sh As Worksheet
    If ComboBox1.ListCount = 0 Then
        For Each sh In Sheets
            If Not Left(sh.Name, 3) = "SEARCH" Then ComboBox1.AddItem sh.Name
        Next
    End If
    End Sub
    
    
    Private Sub CommandButton1_Click()  'Search Part Command Button
    Dim x As String
    Dim lc As Long
    Dim fc As Range
    Dim y As Long
    Dim i As Long
    Dim op As Integer
    Dim tipo As String
    Dim refprod As String
    Dim prod As String
    Dim cod As String
    Dim acab As String
    Dim cor As String
    Dim lote As String
    Dim loc As String
    Dim forn As String
    
    op = 0
    
    If Range("B2").Value <> vbNullString Then   'if the value in cell B2 is not empty then
        x = Range("B2").Value   'set x equal to the value in B2
        y = 1   'y equal to 1 to reference the column B
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("C2").Value <> vbNullString Then   'if the value in B5 is nothing then if the value in C2 is not nothing then
        x = Range("C2").Value   'set x to the value in c2
        y = 2   'set y equal to 1 to reference column C
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("D2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("D2").Value   'set x to the vlaue in D2
        y = 3   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("E2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("E2").Value   'set x to the vlaue in D2
        y = 4   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("F2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("F2").Value   'set x to the vlaue in D2
        y = 5   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("G2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("G2").Value   'set x to the vlaue in D2
        y = 6   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("H2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("H2").Value   'set x to the vlaue in D2
        y = 7   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("I2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("I2").Value   'set x to the vlaue in D2
        y = 8   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    ElseIf Range("J2").Value <> vbNullString Then   ' if the value in B2 and C2 is nothing then if D2 is not nothing then
        x = Range("J2").Value   'set x to the vlaue in D2
        y = 9   'set y equal to 1 to reference column D
        tipo = Range("B2").Value
        refprod = Range("C2").Value
        prod = Range("D2").Value
        cod = Range("E2").Value
        acab = Range("F2").Value
        cor = Range("G2").Value
        lote = Range("H2").Value
        loc = Range("I2").Value
        forn = Range("J2").Value
    Else
        y = 1
        x = "nada"
    End If  'end if statement
    
    
    If tipo = vbNullString Then
        tipo = "*"
    End If
    
    If refprod = vbNullString Then
        refprod = "*"
    End If
    
    If cod = vbNullString Then
        cod = "*"
    End If
    
    If acab = vbNullString Then
        acab = "*"
    End If
    
    If cor = vbNullString Then
        cor = "*"
    End If
    
    If lote = vbNullString Then
        lote = "*"
    End If
    
    If loc = vbNullString Then
        loc = "*"
    End If
    
    If forn = vbNullString Then
        forn = "*"
    End If
    
    If prod = vbNullString Then
        prod = "*"
    End If
    
    
    If Range("U5").Value <> vbNullString Then
        linhas = Range("U5").Value
        Else: Range("U5").Value = 15
    End If
    
    If linhas < 15 Then
            linhas = 15
            Range("U5").Value = 15
    End If
    Dim sh As Worksheet
    If ComboBox1.Text = "" Then
        For Each sh In Sheets
            If Left(sh.Name, 3) = "ARM" Then insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
        Next
    Else: Set sh = Sheets(ComboBox1.Text)
                insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
    End If
    
    
    
    End Sub
    Sub insertsearch(sh As Worksheet, y As Long, x As String, tipo As String, refprod As String, prod As String, cod As String, acab As String, cor As String, lote As String, loc As String, forn As String)
    Dim fc As Range, lc As Long, op As Long, i As Long
    With sh 'with the worksheet whose name is equal to the value selected in the machine combobox in cell a5
        'if the number of times that the value x (defined above based on cell filled in)
        'is found in the column y (defined above) more than 0 times then
        If WorksheetFunction.CountIf(.Columns(y), x) > 0 Then
            Set fc = .Cells(2, y)   'set fc equal to cell in row 2, column y(defined above)
                Range("U7").Value = WorksheetFunction.CountIf(.Columns(y), x)
                'loop through LC from one to the number of time x (defined above) appears in column y(defined above)
                For lc = 1 To WorksheetFunction.CountIf(.Columns(y), x)
                
                    'reset fc to the found cell in column y (defined above) whose value is equal to x (defined above)
                    Set fc = .Columns(y).Find(what:=x, After:=fc, _
                                                    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False)
                                                   
                        'when a value is found provide a message box listing the record found to make sure it is the correct record
                        If UCase(fc(1, 1)) Like UCase(tipo) And UCase(fc(1, 2)) Like UCase(refprod) And UCase(fc(1, 3)) Like UCase(prod) And UCase(fc(1, 4)) Like UCase(cod) And UCase(fc(1, 5)) Like UCase(acab) And UCase(fc(1, 6)) Like UCase(cor) And UCase(fc(1, 7)) Like UCase(lote) And UCase(fc(1, 8)) Like UCase(loc) And UCase(fc(1, 9)) Like UCase(forn) Then
                            If op = 0 Then
                                If MsgBox("╔ este o valor correcto?" & vbNewLine & _
                                    "Tipo de Produto: " & .Cells(fc.Row, 1) & vbNewLine & "Referencia Produto: " & .Cells(fc.Row, 2) & vbNewLine & _
                                    "Produto: " & .Cells(fc.Row, 3) & vbNewLine & "Codigo: " & .Cells(fc.Row, 4) & vbNewLine & _
                                    "Acabamento: " & .Cells(fc.Row, 5), vbYesNo) = vbYes Then
                                        For i = 1 To 18  'loop through the variable i from numbers 1 To 18 (represents column numbers)
                                            'distribute the found information accordingly to row 2 of the Stock worksheet
                                            Cells(2, i + 1) = .Cells(fc.Row, i)
                                        Next i  'move to next i in the i loop
                                        op = 1
                                End If
                            End If
                            
                                Cells(linhas, 1).Value = sh.Name
                            For i = 1 To 18 'loop through the variable i from numbers 1 To 18 (represents column numbers)
                                'distribute the found information accordingly to row 2 of the Stock worksheet
                                Cells(linhas, i + 1) = .Cells(fc.Row, i)
                            Next i  'move to next i in the i loop
    
                            linhas = linhas + 1
                        
                        End If
                        
                Next lc 'move to next lc in the loop
                Range("U5").Value = linhas
        ElseIf x = "nada" Then
                MsgBox "Preencha alguns campos para iniciar pesquisa", vbOKOnly, "Nada"  'if no value is found the msgbox part not available
            Else
            MsgBox "Part Not Available", vbOKOnly, "Not Found"  'if no value is found the msgbox part not available
        End If
    
    End With
    
    End Sub
    Sub StockIn()
    
    If ComboBox1.Value = vbNullString Then  'if the user has yet to search for the record then
        'provide the user with a msgbox asking them to search for the record first
        MsgBox "Tem de fazer primeiro a escolha do produto para dar entrada de stock.", vbOKOnly, "Please Read"
    Else    'if the combobox has a value then
        With Sheets(ComboBox1.Value)    'with the worksheet whose name is equal to combobox1's value
            'cell a5 on the parts store worksheet references the foundcell's row number
            'so the cell in found cell's row, column G(New Stock) is equal to the
            'current value in that cell plus the value entered in E14 of the parts store worksheet
            .Cells(Range("A2").Value, 7) = .Cells(Range("A5").Value, 7) + Range("L5").Value
                    
        End With
        
        'provide message box letting user know the values have been updated
        MsgBox "Stock actualizado", vbOKOnly, "StockIn"
        
    End If
    
    End Sub
    
    Sub StockOut()
    
    If ComboBox1.Value = vbNullString Then  'if the user has yet to search for the record then
        'provide the user with a msgbox asking them to search for the record first
        MsgBox "Tem de fazer primeiro a escolha do produto para dar saida de stock.", vbOKOnly, "Please Read"
    Else    'if the combobox has a value then
        With Sheets(ComboBox1.Value)    'with the worksheet whose name is equal to combobox1's value
            'cell a5 on the parts store worksheet references the foundcell's row number
            'so the cell in found cell's row, column G(New Stock) is equal to the
            'current value in that cell minus the value entered in E14 of the parts store worksheet
            .Cells(Range("A5").Value, 7) = .Cells(Range("A5").Value, 7) - Range("L8").Value
            
        End With
        
        'provide message box letting user know the values have been updated
        MsgBox "Stock actualizado", vbOKOnly, "StockOut"
        
    End If
    
    
    End Sub

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    Dim sh As Worksheet
    If ComboBox1.Text = "" Then
    For Each sh In Sheets
    If Left(sh.Name, 3) = "ARM" Then insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
    Next
    Else: Set sh = Sheets(ComboBox1.Text)
    insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
    End If
    you also need to modify the sheet loop in the search code similarly (near the bottom of command1 click procedure)
    you have not changed this part, it still only searches for sheets named ARM
    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

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    what do i need to add, sorry but my code knowledge sucks.

    tanhks

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    you need to change it the same as in the other place
    Code:
        For Each sh In Sheets
            If not Left(sh.Name, 3) = "SEARCH" Then insertsearch sh, y, x, tipo, refprod, prod, cod, acab, cor, lote, loc, forn
        Next
    but my code knowledge sucks.
    so who wrote the rest of the code?
    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

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    several searches in lots of foums. you know (show me the way, and i will walk)

    but most of it was my cousin, bu now he is working and as no time to help me any more

    but thanks any way

    rsilva

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    ok westconn

    it┤s allmost done the search procedure, but there's something missing couse if i search for some item with asterics its gives no results and if i search for "teste" the last item i inserted it work i dont understand why.

    heres's the file again

    by the way do you have any idea to get the stock in and out working properlty allready has the buttuns and code but still need to difine something like a checkbox or selectable item to make the movement.

    tanks again for being so paciant

    rsilva
    Attached Files Attached Files

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi westconn i noticed that to search i still need to fill the second row is there a way to remove that

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    from post #9
    but so far it is not returning the values correctly, which is caused by the line comparing the values
    that was the result i got when leaving the all fields blank except leather
    you will need to test why it is not working on the line i indicated
    it is a terrible line of code that, to test, will have to be broken down into individual conditions and each evaluated to find why it is returning false

    by the way do you have any idea to get the stock in and out working properlty allready has the buttuns and code but still need to difine something like a checkbox or selectable item to make the movement.
    as you have not explained what they are supposed to do, hard to guess how to make work
    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

  24. #24

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Cool Re: Stock control working with vb code (almost done just a few tweaks)

    This part is easy (i think), just need to update the quantity (QT.) row in the stock

    For exemple it could have several checkbox in the search result, and when we press stockin or stockout it changes the value of the row quantity

    i made some changes to the sheet see if you understand.

    that was the result i got when leaving the all fields blank except leather
    you will need to test why it is not working on the line i indicated
    it is a terrible line of code that, to test, will have to be broken down into individual conditions and each evaluated to find why it is returning false
    As for this i will make some tests, where do you sugest i should start, you know my Vb code sucks, zero at code.


    thanks for your help and time

    rsilva
    Attached Files Attached Files

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i changed this line seems to work, but requires much testing
    Code:
                    Set fc = .Columns(y).Find(what:=x, After:=fc(1, 5), _
                                                    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False).Offset(, -y + 1)
    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

  26. #26

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    Code:
    Sub insertsearch(sh As Worksheet, y As Long, x As String, tipo As String, refprod As String, prod As String, cod As String, acab As String, cor As String, lote As String, loc As String, forn As String)
    Dim fc As Range, lc As Long, op As Long, i As Long
    With sh 'with the worksheet whose name is equal to the value selected in the machine combobox in cell a5
        'if the number of times that the value x (defined above based on cell filled in)
        'is found in the column y (defined above) more than 0 times then
        If WorksheetFunction.CountIf(.Columns(y), x) > 0 Then
            Set fc = .Cells(2, y)   'set fc equal to cell in row 2, column y(defined above)
                Range("U7").Value = WorksheetFunction.CountIf(.Columns(y), x)
                'loop through LC from one to the number of time x (defined above) appears in column y(defined above)
                For lc = 1 To WorksheetFunction.CountIf(.Columns(y), x)
                
                    'reset fc to the found cell in column y (defined above) whose value is equal to x (defined above)
                     Set fc = .Columns(y).Find(what:=x, After:=fc(1, 5), _
                                                    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False).Offset(, -y + 1)
    i made the search for "serrado" only gives me error "type mistach" on the code you told me to insert.

    Rsilva

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i found i had to change this line
    Code:
    Set fc = .Cells(2, 1)   'set fc equal to cell in row 2, column y(defined above)
    it can be very difficult trying to fix problems in other peoples code, as i said before you will have to test every possibility of search
    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

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    yes and i'm wiling to do it and sorry to be allways asking you

    but thanks, if you lived near me i whould pay you a beer

  29. #29

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    ok it's working fine on "acabamento" even if i creat new one it works.
    But in the other rows it doesn't it gives type missmatch again

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    if i search only for type of product it wont work any more.

    damm starts to work on one side and on the other stops lololo
    Last edited by ricardo_mcs; Nov 21st, 2012 at 08:54 AM.

  31. #31

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    Hi westconn

    I'm trying to understand on my own the code to make it work but as usual i'm block.

    If i search for someting under "acabamento" the code works and gives the expected results,

    but if i try to search for other item in other row it just gives me the error "type mismatch"

    and making debug goes into this line of code.

    Code:
    'reset fc to the found cell in column y (defined above) whose value is equal to x (defined above)
                         Set fc = .Columns(y).Find(what:=x, After:=fc(1, 5), _
                                                    LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, MatchCase:=False).Offset(, -y + 1)


    If i understand well this
    Code:
    After:=fc(1, 5),
    the "5" refences to the row it self,

    Now if i duplicate the values in the 1║ piece of code and change this value some how, won't it work for the other row's that result in error becouse all of them point to this line of i try to search for someting.


    thanks

    Rsilva

  32. #32

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    Good morning.

    If someone could help me, i whould be very apriciated.

    I'm almost at the end of the program just need to see this
    "type mismatch" error, only "acabamento" works wel

    the updated file is the last posted here

    tanks

    rsilva

  33. #33
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    i have done some minor testing to convert the search code to use sql queries
    it would appear to work much better, but i would still have to build the query strings, if you want, i can work on it over the next few days, if i have any spare time

    i am sure sql would be much more stable, even when working with worksheet data
    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

  34. #34

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi Westconn

    Thanks for the idea, but I think it's better to keep it this way, cause 1║ the database wont be much larger than already is, and this works just fine,

    but you know all ideas are welcome.

    I would like now to get the buttons STOCK IN and STOCK OUT working

    I think this is he only thing missing on the program

    Thanks

    Rsilva

  35. #35
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,065

    Re: Stock control working with vb code (almost done just a few tweaks)

    and this works just fine,
    i thought it was not working as you wanted, for search by column

    if it is all working correctly now, pls mark thread resolved
    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

  36. #36

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    25

    Re: Stock control working with vb code (almost done just a few tweaks)

    hi, westconn

    the search works well but, i have allways to select the type of product .

    but for now it's ok .

    but there is one thing i need to get working, that is the 2 buttons stock in and stock out .

    i really have no idea how to do it.

    thanks

    rsilva

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.