CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    [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
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Nov 2012
    Posts
    5

    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

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Nov 2012
    Posts
    5

    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

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Nov 2012
    Posts
    5

    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
    Attached Files Attached Files

  8. #8
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  9. #9
    Join Date
    Nov 2012
    Posts
    5

    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

  10. #10
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Stock control working with vb code (almost done help with error)

    Is there a problem? What, WHERE
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  11. #11
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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
    Always use [code][/code] tags when posting code.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured