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

hi


anyone could give a look please,

just need a small code correction

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

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

here's the file

thank´s

Rsilva

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

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

Attachment 93261

hope you understand

thanks

rsilva

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

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

Quote:

---

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

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

Quote:

---

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

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

Quote:

---

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)

Quote:

---

search results in the wherehouse columm when a search is made

---

add

Code:

---

cells(linhas, 1).value = sh.name

---

before

Quote:

---

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

---

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

---

Quote:

---

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

---

Quote:

---

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

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

tanhks

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

---

Quote:

---

but my code knowledge sucks.

---

so who wrote the rest of the code?

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

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

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

from post #9

Quote:

---

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

Quote:

---

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

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.

Quote:

---

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

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)

---

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

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

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

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

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

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

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

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

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

Quote:

---

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

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