Need help with formula

Thank you Matt, this works as I needed. :-)

Try using this instead.  I cleaned up the code for you.  Typically you don't want to use ActiveCell or Activate or Select .  You can work with worksheet objects directly (see below):

Private Sub CommandButton1_Click()

    Dim InvoiceNumber    As Long
    Dim CustomerName     As String
    Dim InvoiceDate      As String
    Dim SampleNumber     As String
    Dim AmountInvoice    As String
    Dim shtInv           As Worksheet
    Dim shtInvList       As Worksheet
    Dim InvListLRow      As Long
    
    'Define worksheets
    Set shtInv = Worksheets("Invoices")
    Set shtInvList = Worksheets("Invoice List")
    
    'Pass values to variables
    InvListLRow = shtInvList.Cells(shtInvList.Rows.Count, "A").End(xlUp).Row 'Define last occupied row on Invoice List Sheet
    InvoiceNumber = shtInv.Range("J11")
    CustomerName = shtInv.Range("B1") ' I think this is the wrong cell reference...may want to check
    InvoiceDate = shtInv.Range("I9")
    SampleNumber = shtInv.Range("I9")
    AmountInvoice = shtInv.Range("J27")

    With shtInvList
        .Range("A" & InvListLRow + 1) = InvoiceNumber
        .Range("B" & InvListLRow + 1) = CustomerName  'This had a typo...
        .Range("C" & InvListLRow + 1) = InvoiceDate
        .Range("D" & InvListLRow + 1) = SampleNumber
        .Range("E" & InvListLRow + 1) = AmountInvoice
    End With
    
    shtInv.Activate 'Activate Invoices Worksheet
    shtInv.PrintOut 'Print
        
End Sub

Hi Matt,

It did not take the data from Invoice Tab to add to the Invoice List Tab :-(

The data from Invoice tab which should be added into the invoice list is Invoice Number, Customer Name, Invoice Date, Sample Number and total amount invoiced incl VAT.

Regards

Adam

It looks like you had your If/End If statement in the wrong place:

   If Worksheets("Invoice_List").Range("A1").Offset(1, 0) <> "" Then
    End If

Try this:

Private Sub CommandButton1_Click()

    Dim InvoiceNumber As Long
    Dim CustomerName As String
    Dim InvoiceDate As String
    Dim SampleNumber As String
    Dim AmountInvoice As String
    
    Worksheets("Invoices").Select
    InvoiceNumber = Range("J11")
    CustomerName = Range("B1")
    InvoiceDate = Range("I9")
    SampleNumber = Range("I9")
    AmountInvoice = Range("J27")
    Worksheets("Invoice_List").Select
    Worksheets("Invoice_List").Range("A1").Select
    
    If Worksheets("Invoice_List").Range("A1").Offset(1, 0) <> "" Then

        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = InvoiceNumber
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = CustomberName
        ActiveCell.Offset(0, 2).Select
        ActiveCell.Value = InvoiceDate
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = SampleNumber
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = AmountInvoice
        Worksheets("Invoice").Select
        Worksheets("Invoices").Range("J11").Select
        ActiveSheet.PrintOut
        
    End If

End Sub

 

Hi Matt,

I need your help with the following Macro form some reason it is not working.

Private Sub CommandButton1_Click()
Dim InvoiceNumber As Long
Dim CustomerName As String
Dim InvoiceDate As String
Dim SampleNumber As String
Dim AmountInvoice As String
Worksheets("Invoices").Select
InvoiceNumber = Range("J11")
CustomerName = Range("B1")
InvoiceDate = Range("I9")
SampleNumber = Range("I9")
AmountInvoice = Range("J27")
Worksheets("Invoice_List").Select
Worksheets("Invoice_List").Range("A1").Select
If Worksheets("Invoice_List").Range("A1").Offset(1, 0) <> "" Then
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = InvoiceNumber
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomberName
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = InvoiceDate
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = SampleNumber
ActiveCell.Offset(0, 4).Select
ActiveCell.Value = AmountInvoice
Worksheets("Invoice").Select
Worksheets("Invoices").Range("J11").Select
ActiveSheet.PrintOut

End Sub

As you can see I am trying to take data from one sheet to another and then print the active sheet (Invoices)

Can you see what I am doing wrong on the coding as I am trying to assist a colleague on this.

Thanks

Adam

Thanks for the update on the issue and fixing it Matt.

Have a nice weekend.

Regards

Adam

Adam-

It appears that some of the percentages where being represented as text.  Therefore they could not be evaluated with > or < operators.  The best way to identify things like this is to use the Evaluate Formula option.  (Data Tab > Evaluate Formula).  When you click evaluate on the formula several times you will notice that some of the numbers are wrapped in double quotes. (i.e. ".05%")  This means that Excel is reading this value as text.  In order to fix the issue I reformatted the values on the Peerless worksheet as well as the MTC Certificate.  Once these values were changed to numbers it appears that the formula worked correctly.  I made a few tolerance tables in my example in order to better understand why the Steel Passed or Failed.  I'm attaching it for review.  This file has all of the formatting fixed appropriately as well.

Hi Matt,

Can you help me with the following attachment..my fellow student is using your formula under the MTC or MTC Certificate for the test result overall to either pass or fail but for some reason it is not working, can you please check it out and advise me where I am going wrong on this as my one is working perfectly.

Thanks

Adam

Adam-

1. Delete the Button1 Code Module

2. Delete all code in the Process Invoices Code Module

3. Paste this code in the Process Invoices Code Module

Sub Process_Invoices()

    Dim Lrow As Integer

    'Print Worksheet
    ActiveSheet.PrintOut
    
    'Define Last Row of Invoice List
    Lrow = Sheets("Invoice List").Cells(Sheets("Invoice List").Cells.Rows.Count, "A").End(xlUp).Row
   
    'Put Invoice value on Invoice List
    Sheets("Invoice List").Range("A" & Lrow + 1) = Sheets("Invoice").Range("F4").Value
   
    'Increment Invoice
    Sheets("Invoice").Range("F4") = Sheets("Invoice").Range("F4") + 1
   
    'Put Invoice associated values on Invoice List
    Sheets("Invoice List").Range("B" & Lrow + 1) = Sheets("Invoice").Range("F6").Value 'Date
    Sheets("Invoice List").Range("C" & Lrow + 1) = Sheets("Invoice").Range("E15").Value 'Sample ID
    Sheets("Invoice List").Range("D" & Lrow + 1) = Sheets("Invoice").Range("B15").Value 'Company Name
    Sheets("Invoice List").Range("E" & Lrow + 1) = Sheets("Invoice").Range("F33").Value 'Invoice Amount
    Sheets("Invoice List").Range("F" & Lrow + 1) = Sheets("Invoice").Range("F34").Value 'VAT
    Sheets("Invoice List").Range("G" & Lrow + 1) = Sheets("Invoice").Range("F35").Value 'Total Charge
   
End Sub

4. Right Click The Print Button

5. Select Assign Macro

6. Select  Process_Invoices Procedure

7. Now your button will do both

HI Matt,

Thanks for the advice, so is there no way of combining the following Macros.

ub Process_Invoices()

    Dim Lrow As Integer
   
    Lrow = Sheets("Invoice List").Cells(Sheets("Invoice List").Cells.Rows.Count, "A").End(xlUp).Row
   
    Sheets("Invoice List").Range("A" & Lrow + 1) = Sheets("Invoice").Range("F4").Value
   
    Sheets("Invoice").Range("F4") = Sheets("Invoice").Range("F4") + 1
   
    Sheets("Invoice List").Range("B" & Lrow + 1) = Sheets("Invoice").Range("F6").Value 'Date
    Sheets("Invoice List").Range("C" & Lrow + 1) = Sheets("Invoice").Range("E15").Value 'Sample ID
    Sheets("Invoice List").Range("D" & Lrow + 1) = Sheets("Invoice").Range("B15").Value 'Company Name
    Sheets("Invoice List").Range("E" & Lrow + 1) = Sheets("Invoice").Range("F33").Value 'Invoice Amount
    Sheets("Invoice List").Range("F" & Lrow + 1) = Sheets("Invoice").Range("F34").Value 'VAT
    Sheets("Invoice List").Range("G" & Lrow + 1) = Sheets("Invoice").Range("F35").Value 'Total Charge
   
End Sub

and

Sub Button1_Click()
ActiveSheet.PrintOut
End Sub

..

Would I need to create a new Module to do below what you are asking.

Module 1 says Button1_Click

Module 2 says Process_Invoices

Regards

Adam

The one macro just updates the invoice list.  The other one you have prints the document I guess?

What you need to do is combine the code to do it all in one step.  I would say you need to make a Sub Procedure like this that will run both procedures in succession:

Sub RunBothProcs()
    'You need to change the below procedure names and then link this
    'new procedure to your button.
    Call PrintMacro 
    Call UpdateList
End Sub

Would that also Print the invoice as well by doing this? or just update the Invoice List with this macro?

Adam-

In order to access the code:

1. right click on the button
2. Click on Assign Macro
3. Click Edit

Thanks for the update Matt, that button on the invoice tab, how do I see what has been programmed as when I try to press the Print Preview button at the buttom of the Invoice Tab Cell 46-47?

Since that button is not working now with this formula so I would need to modify that one.

Thanks

Adam

Adam-

You can name the Sub Procedure whatever you like, just no spaces or special characters.  Underscores are acceptable.  Here are some examples:

Sub Whatever()
    'Your Code Here
End Sub

Sub Process_Invoices()
    'Your Code Here
End Sub

Sub Adams_Procedure()
    'Your Code Here
End Sub

Morning Matt,

The formula and all looks good thank you :-)

Does the following need to have he word Test () - Sub Test() or can I remove the word Test from coding?

Thanks

Adam