run time error 13 Type mismatch

[GEORGE PERRY]

Please i need help. my Excel VBA Application is giving this message

run time error 13 Type mismatch

Sub show_inventory()

Dim sh As Workbook
Set sh = ThisWorkbook.Sheets("Inventory")

sh Cells.Clear
ThisWorkbook.Sheets("Product_master").Range("B:B").Copy sh.Range("A1")
sh.Range("B1").Value = "Purchase"
sh.Range("C1").Value = "Sale"
sh.Range("D1").Value = "Available Stock"
sh.Range("E1").Value = "Stock Value"

Dim lr As Long
lr = Application.WorksheetFunction(sh.Range("A:A"))

If lr > 1 Then
sh.Range("B2").Value = "=SUMIFS(SALE_PURCHASE!D:D,SALE_PURCHASE!B:B,INVENTORY!A2,SALE_PURCHASE!C:C,""PURCHASE"")"
sh.Range("C2").Value = "=SUMIFS(SALE_PURCHASE!D:D,SALE_PURCHASE!B:B,INVENTORY!A2,SALE_PURCHASE!C:C,""SALE"")"
sh.Range("D2").Value = "B2-C2"
sh.Range("E2").Value = "=VLOOKUP(A2,PRODUCT_MASTER!B:C,2,0)*D2"

If lr > 2 Then
sh.Range("B2:E" & lr).FillDown

sh.Calculate
End If

sh.UsedRange.Copy
sh.UsedRange.PasteSpecial xlPasteValues

Dim inv_Display As Worksheet
Set inv_Display = ThisWorkbook.Sheets("Inventory_Display")
inv_Display.Cells.Clear

If Me.Txt_Search.Value <> "" Then
    sh.UsedRange.AutoFilter 1, "*" & Me.Txt_Search.Value & "*"
End If

sh.UsedRange.Copy inv_Display.Range("A1")

[rollis13]

Have a try with these four marked fixes to your code (but think there also are other issues in other parts of the project):

Sub show_inventory()    
    Dim sh     As Worksheet                       '<= changed was: Workbook
    Set sh = ThisWorkbook.Sheets("Inventory")
    sh.Cells.Clear                                '<= changed was: sh Cells.Clear
    ThisWorkbook.Sheets("Product_master").Range("B:B").Copy sh.Range("A1")
    sh.Range("B1").Value = "Purchase"
    sh.Range("C1").Value = "Sale"
    sh.Range("D1").Value = "Available Stock"
    sh.Range("E1").Value = "Stock Value"
    Dim lr     As Long
    lr = sh.Range("A" & sh.Rows.Count).End(xlUp).Row '<= changed
    'was: lr = Application.WorksheetFunction(sh.Range("A:A"))
    If lr > 1 Then
        sh.Range("B2").Value = "=SUMIFS(SALE_PURCHASE!D:D,SALE_PURCHASE!B:B,INVENTORY!A2,SALE_PURCHASE!C:C,""PURCHASE"")"
        sh.Range("C2").Value = "=SUMIFS(SALE_PURCHASE!D:D,SALE_PURCHASE!B:B,INVENTORY!A2,SALE_PURCHASE!C:C,""SALE"")"
        sh.Range("D2").Value = "B2-C2"
        sh.Range("E2").Value = "=VLOOKUP(A2,PRODUCT_MASTER!B:C,2,0)*D2"
        If lr > 2 Then
            sh.Range("B2:E" & lr).FillDown
            sh.Calculate
        End If
        sh.UsedRange.Copy
        sh.UsedRange.PasteSpecial xlPasteValues
        Dim inv_Display As Worksheet
        Set inv_Display = ThisWorkbook.Sheets("Inventory_Display")
        inv_Display.Cells.Clear
        If Me.Txt_Search.Value <> "" Then
            sh.UsedRange.AutoFilter 1, "*" & Me.Txt_Search.Value & "*"
        End If
        sh.UsedRange.Copy inv_Display.Range("A1")
        '''''''''''Show Data'''''''
        lr = inv_Display.Range("A" & inv_Display.Rows.Count).End(xlUp).Row '<= changed
        'was: lr = Application.WorksheetFunction(inv_Display.Range("A:A"))
        If lr = 1 Then lr = 2
        With Me.ListBox1
            .ColumnCount = 5
            .ColumnHeads = True
            .ColumnWidths = "150,0,0,80,0"
            .RowSource = inv_Display.Name & "!A2:E" & lr
        End With
    End If
End Sub

[Dave]

Dim sh As Worksheet

HTH. Dave

[snb]

While using VBA you still are thinking in Excel.

Sub show_inventory()
 With ThisWorkbook.Sheets("Inventory")
   .Cells.Clear
   ThisWorkbook.Sheets("Product_master").Range("B:B").Copy .cells(1)
   .Range("B1:E1").Value = array("Purchase","Sale","Available Stock","Stock Value")
 end with

  - - - - -

[GEORGE PERRY]

thanks a lot much appreciated.

[GEORGE PERRY]

please help me on this:

Compile error
Method or data member nor found

Sub show_Numbers()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Report")

sh.Range("C1").Value = Me.txt_Start_Date.Value
sh.Range("C2").Value = Me.txt_End_Date.Value
sh.Calculate

Me.Lbl_Purchase.Caption = sh.Range("C4").Value
Me.lbl_Sale.Caption = sh.Range("C5").Value
Me.lbl_Profit.Caption = sh.Range("C6").Value
Me.lbl_Inventory_Qty.Caption = sh.Range("C7").Value
Me.lbl_Inventory_Amt = sh.Range("C8").Value

End Sub

[snb]

Use With ... End With; no need for redundant variables.

[SamT]

That UserForm, (frm_Drug_Store_Management,) does not have a Label "lbl_Sale" .It does have a lbl_Sale9. However, you somehow managed to make "lbl-Sale9" act as an alias for Image5.
In fact all your so called Labels in that sub are actually un-named Images. I don't know how you did it. It may be a glitch translating from xlsm to xls.

IN THE FUTURE... Please tell us which line Raises the Error and which Module the Procedure is in.

[GEORGE PERRY]

thanks. issues resolved now.

But if i run the whole app, i expect the app to show the rate if Sale or Purchase is selected in the drop down list.
but it is not giving that. and i don't know where the mistake is from.

please any help on that?
attached is the excel app.