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
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
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
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
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"
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
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
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
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
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
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
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.
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
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.
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
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.
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
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.
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
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