TRANSACTION RATE NOT SHOWING

[GEORGE PERRY]

Please i need help on this. I want my excel app to show the rate for product sale or purchase if purchase or sale is selected. but it does not show when sale or purchase is selected from the drop down of transaction type.
Profit, Sale and the Purchase too not showing while the inventory quantity and amount are showing

Private Sub Cmb_Product_Change()

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

If Me.Cmb_Product.Value = "" Or Me.cmb_Type.Value = "" Then Me.txt_Rate.Value = ""

If Me.cmb_Type.Value = "Sale" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf Me.cmb_Type.Value = "Purchase" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 3, 0)
End If

End Sub

Private Sub cmb_Type_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Master")

If Me.Cmb_Product.Value = "" Or Me.cmb_Type.Value = "" Then Me.txt_Rate.Value = ""

If Me.cmb_Type.Value = "Sale" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf Me.cmb_Type.Value = "Purchase" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 3, 0)
End If

End Sub



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.Caption = sh.Range("C8").Value


End Sub

[Kenneth Hobs]

Use Debug.Print x where x is your vLookup lines to see what they are evaluating to or debug line by line (F8). View Immediate Window in VBE after the Run.

I had to add vba. to your Date and Format commands to make those seen as vba. That may be due to the missing addin references.

I can look at this later tonight. If still stuck, post back and detail the steps and values to add to the userform to get the problem and what the result should be.

Nice looking userform graphic layout...

[SamT]

Nice looking Forms, but their code is way to complex because you are making the UserForms do all the work. This requires thinking in many dimensions at the same time

I tried, but I can't see any errors in the code in question.

It is my Personal Coding style to move as much work as possible to the sheets' Code Pages and treat them as Class Modules using Properties and Methods.

Imagine Worksheet("Product_Master")'s Code Name Was changed to "ProductMaster" and the Code page had the Method "Rate".
Example:
Dim ProductsCol As Range '(= Range("B:B")) 'Must set Ranges in Class Initialize Sub

Public Function Rate(TransactionType, ProductName) As String
Dim OffsetValue as Long

'Simple Test
If TransActionType = "" or ProductName = "" Then
Rate = ""
Exit Function
End If

If TransActionType = "Sale" Then OffsetValue = 1
If TransactionType = "Purchase" Then OffsetValue = 2

Rate = ProductsCol.Find(ProductName).Offset(, OffsetValue)
End FunctionThis also allows local testing of the Function. Example:
Sub Test_Rate()
Dim X, Y
X = Me.Rate("Sale", "IBUCAP")
Debux.Print X
Y = Me.Rate("Purchase", "IBUCAP")
Debux.Print Y
End Sub

Then your Form's Product_Change code could be
Private Sub Cmb_Product_Change()
Me.txt_Rate.Value = ProductMaster.Rate(Me.cmb_Type, Me.Cmb_Product)
End Sub
If there was an issue, anyone looking at the Code would immediately know to see the "ProductMaster" Class and find the simple, one dimension "Rate" Procedure.

[GEORGE PERRY]

HI seniors,

after using all the directives given after my post is till hve the same issues which i can't understand. i am a learner and i need your support.
when i select PURCHASE or SALE from the TRANSACTION TYPE on the user form, i expect the RATE of the Product to show. its not been so.
and on my REPORT SHEET too, i want the PURCHASE,SALE and PROFIT to show as well as the INVENTORY AMT and INVENTORY QTY.
Here are the code i have for the PRODUCT CHANGE AND TRANSACTION TYPE and for the REPORT SHEET TOO



Private Sub Cmb_Product_Change()

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

If Me.Cmb_Product.Value = "" Or Me.cmb_Type.Value = "" Then Me.txt_Rate.Value = ""

If Me.cmb_Type.Value = "Sale" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf Me.txt_Rate = "Purchase" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 3, 0)
End If

End Sub

Private Sub cmb_Type_Change()

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

If Me.Cmb_Product.Value = "" Or Me.cmb_Type.Value = "" Then Me.txt_Rate.Value = ""

If Me.cmb_Type.Value = "Sale" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf Me.txt_Rate = "Purchase" Then
Me.txt_Rate.Value = Application.WorksheetFunction.VLookup(Me.Cmb_Product, sh.Range("B:D"), 3, 0)
End If
End Sub



REPORT SHEET CODE


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.Caption = sh.Range("C8").Value

End Sub

[Kenneth Hobs]

For the first Sub, you are comparing PRODUCT to Product. As a binary compare, that would never be equal. In other words, it is case sensitive. You can either live with that and be careful in your string comparisons or skip the worry and use Text compare where it is not case sensitive as shown in this example.

Note the structural changes in your code as well. I always use Option Explicit and Debug > Compile before a Run. Since both Change events did the same thing, I just called that routine. Option lines go at the top of your Userform, Module, etc.


Option Explicit
Option Compare Text


Private Sub Cmb_Product_Change()
sCalcRate
End Sub


Private Sub cmb_Type_Change()
sCalcRate
End Sub


Private Sub sCalcRate()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Product_Master")

If Cmb_Product = "" Or cmb_Type = "" Then txt_Rate = ""


If cmb_Type = "SaLe" Then
txt_Rate = WorksheetFunction.VLookup(Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf cmb_Type = "PURCHASE" Then
txt_Rate = WorksheetFunction.VLookup(Cmb_Product, sh.Range("B:D"), 3, 0)
End If
End Sub

[GEORGE PERRY]

Thanks a lot for the directives, much appreciate

TRANSACTION TYPE RATE NOW SHOWING using the OPTION EXPLICIT......
BUT another message appears when the type is selected with yellow highlight

runtime error 1004
Unable to get the vlookup prooerty of the worksheetFunction class


Option Explicit
Option Compare Text

Private Sub Cmb_Product_Change()
sCalcRate
End Sub
Private Sub cmb_Type_Change()
sCalcRate
End Sub

Private Sub sCalcRate()
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Product_Master")

If Cmb_Product = "" Or cmb_Type = "" Then txt_Rate = ""

If cmb_Type = "Sale" Then
txt_Rate = Application.WorksheetFunction.VLookup(Cmb_Product, sh.Range("B:D"), 2, 0)
ElseIf cmb_Type = "Purchase" Then
txt_Rate = Application.WorksheetFunction.VLookup(Cmb_Product, sh.Range("B:D"), 3, 0)
End If
End Sub :banghead:

[Kenneth Hobs]

Let me guess, you picked the blank value for Cmb_Product? As the coder, it is your job to code as best you can to avoid likely errors. For this case, you need to fill the combobox with only "valid" existing values.

Please don't use yellow font color. Use red to make it stand out or just post that single line of code that caused a problem.

As with any routine, you will need to handle run-time errors in your code if you want to avoid seeing them. vLookup is one that I have dealt with before. The Range("Whatever").Find method can error too. One can check if the returned range is Nothing to handle that.

[snb]

You'd better improve the coding by avoiding clumsy coding.

In Product Master:


Private Sub userform_Initialize()
ListBox2.List = Sheets("Product_Master").ListObjects(1).DataBodyRange.Value
End Sub

Private Sub ListBox2_Change()
If ListBox2.ListIndex > -1 Then
txt_Id = ListBox2.Column(0)
txt_Product = ListBox2.Column(1)
txt_Purchase_Price = ListBox2.Column(2)
txt_Sale_Price = ListBox2.Column(3)
End If
End Sub

Use dynamic Tables in the sheets: e.g. Product Master
Use Design Mode to set Listbox2 prpeerties, Columncount, columnwidths, etc.
Do not use rowsource.
Do not use 'Call', it's redundant.
Do not use Excel functions (like vlookup or sumif) in VBA. In VBA you can perform these actions much faster.