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.