1 Attachment(s)
[RESOLVED] Stock control working with vb code (almost done help with error)
hi.
I'm developing an excel sheet "stock control" with VB code.
The sheet layout is ready and the code in working well
but it only works if i select the row "acabamento=finishing(tranlation)"
if i select the others it gives me "type mismatch" error.
The intent of the program is only to insert values in the search location and then by hiting search it returns the result i need
the buttons "stockin and stockout" are not ready yet (just in case)
i 've been developing the code with a friend but now he can not help me becouse he is out of the country
if anyone could help me i hould be very mutch apriciated
tanks in advance
rsilva
Re: Stock control working with vb code (almost done help with error)
I might could help if you showed the code where you are having a problem. To much trouble to download, extract and then search for it when it could have simply been posted in your message above.
Re: Stock control working with vb code (almost done help with error)
Hi DataMiser
i think the problem is in this part
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)
this part works but it only searches in "acabamento" i don't now why
just in case my knowledge in VB code is very bad the code that is writen, was extrated from other sheets and arranged by my freind, but he can no longer help me
hope you can help me.
rsilva
i've copied the full code, you might need to see its logic
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 = ""
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 Not Left(sh.Name, 3) = "SEARCH" 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, 1) '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)
'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
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 no minimo um campo para iniciar a pesquisa.", vbOKOnly, "Nada" 'if no value is found the msgbox part not available
End If
End With
End Sub
Re: Stock control working with vb code (almost done help with error)
I did not notice before that you were talking about Excel VBA instead of VB.
Afriad I can not be of much help here as I do not write apps in Excel. I always use VB and a database for this kind of thing so....
There are a few users here who do Excel so someone else will likely jump in and give you a hand.
Re: Stock control working with vb code (almost done help with error)
ok friend thanks any way
just need to ask you a favor, if You could pass the word of this treath as you are a power poster.
thanks
Rsilva
Re: Stock control working with vb code (almost done help with error)
ElseIf statements are always harder, especially if you don't properly indent for each level. That makes it easier for us to spot.
Also, rather than single ELSEIF, you could COMBINE fields to check for blank. 1 AND 2 AND 3, etc....
Re-write the logic to see if you could do better, or record a MACRO to see how excel does it.
1 Attachment(s)
Re: Stock control working with vb code (almost done help with error)
Hi
i've done some improvements and it seems to work fine
i've uploaded the excel sheet to see how it works
now i'm starting to work on the "stockin" "stockout"
Any ideas
thanks
Rsilva
Re: Stock control working with vb code (almost done help with error)
Nobody wants to download, unzip, and test your code. Post code that you have questions with, but remember to use CODE TAGS.
Re: Stock control working with vb code (almost done help with error)
Hi
Sorry my mistake
heres the code hope someone can helpme
Code:
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
'so the cell in found cell's row, column G(New Stock) is equal to the
.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
'so the cell in found cell's row, column G(New Stock) is equal to the
.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 help with error)
Is there a problem? What, WHERE
Re: Stock control working with vb code (almost done help with error)
Code:
now i'm starting to work on the "stockin" "stockout"
Any ideas
My suggestion would be don't use Excel
