Invoice totals to weekly order sheet

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
Hi I'm new here and a total VBA beginner.

I'm currently helping a friend of mine get his fruit and vegetable delivery business off the ground by building him a very simple invoicing system. So far I have gotten by with simply lurking here and adapting code as required. The final step I need to make is to get my invoice to transfer the quantity ordered into the weekly running total sheet based on the items ordered and delivery day. Delivery days are in the columns of the total sheet and the items ordered are in the rows.

I currently have this piece of code which runs from a button on the spreadsheet and saves, prints then clears the invoice.

VBA Code:
Sub Macro1()
  Application.Dialogs(xlDialogPrint).Show
    Dim NewFN As Variant
    NewFN = "D:\MARK\Invoices\Invoice" & Range("E4").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    Range("E4").Value = Range("E4").Value + 1
    Range("b7").ClearContents
    Range("B14:B33").ClearContents
    Range("c14:c33").ClearContents
End Sub
My column variable is in cell E7.
My row variables are in cells C14-C33.
The data I want transferred to sheet 1 is in cells B14-B33

This is what I currently have.

VBA Code:
Sub AddValue()
Dim column as String, row As String
Dim c As Single, r As Single
With Worksheets("Sheet1")
If .Range("E7").Value = "" Or .Range("C14").Value = "" Then Exit Sub
column = .Range("E7").Value
row = .Range("C14").Value
column = Application.Match(column, .Range("D1;f1;h1;j1;l1 "), 0)
 row = Application.Match(row, .Range("C4:C101"), 0)
.Range("A1").Offset(r, c).Value = .Range("B14").Value
End With
End Sub
I pulled it from the internet and modified it, but I'm sure that I've messed it up somewhere because it doesn't appear to actually do anything. I also need it integrated into the first piece of code, it can run at any point before the form is cleared.

I'm well and truly in over my head on this and would greatly appreciate any help I can get with this.

Thank you to anyone able to help in advance.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,537
Office Version
2019
Platform
Windows
For a lurking beginner you've done well :)

Rather than fixing the problem for you, I'll try pointing you in the right direction to start with and see how you get on from there.

A couple of pointers, row and column are vba keywords / /commands, using them as variable names is generally considered a bad idea, it can lead to problems and confusion. You will notice that many people use terms like myRow, theRow, oneRow, etc for variables in order to distinguish them from keywords.

Declaring c and r as Single should work, but declaring them as Long would be considered normal. Single is only needed for decimal values, row and column numbers will always be integers (Single has been made redundant by Double and Integer has been made redundant by Long).

I notice that you have set the values of the variables, row and column twice, but not set any values to r and c.
I don't think that the first Match will work with the way that you have set the range. Personally, I would use the Range.Find method instead of Application.Match, getting the .Row and .Column of each find result to r and c respectively, then using them in .Cells(r, c) instead of Range("A1").Offset(r, c).

Hope that at least some of that makes sense.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
Thank you for your help, I've now spent the last half an hour trying to get my head around your reply. I don't know if it's too late on a Sunday night and I'm too tired but I'm really not getting it. So far I have been able to find pieces of code which I can modify to achieve what I require. This one I'm really struggling with, I might just need to sleep on it and come back to it another time.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,537
Office Version
2019
Platform
Windows
late on a Sunday night
Still daytime on my side of the globe, I'll have another look at your code and rewrite it for you later so you'll have something to try in the morning, hopefully it will make more sense when you see it.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
Thank you very much, I'm constantly feeling like I'm ordering off a menu written in french, I know what some of it means and I can pick out patterns, but I really can't read the whole thing.
In saying this though even though it has been a very steep learning curve it has been very satisfying to see the whole project coming together, but I have been stuck on this one piece of code for a few days. I haven't really had much to do with VBA since I was in high school which was longer ago than I would like to admit. Again thank you for your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,537
Office Version
2019
Platform
Windows
I haven't tested this but think it should be ok. There is no error test so if the match string is not found then it will fail.
VBA Code:
Sub AddValue()
Dim cVal As String, rVal As String
Dim c As Long, r As Long
With Worksheets("Sheet1")
    If .Range("E7").Value = "" Or .Range("C14").Value = "" Then Exit Sub
    cVal = .Range("E7").Value
    rVal = .Range("C14").Value
    c = .Range("D1,f1,h1,j1,l1").Find(cVal, , xlValues, xlWhole, , False).column
    r = .Range("C4:C101").Find(rVal).row
    .Cells(r, c).Value = .Range("B14").Value
End With
End Sub
You don't need to integrate it into your other code, just add the line Call AddValue into Macro1 at the point where you want it to run.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
Thank you very much, I’ll give it a try when I get home from work tonight.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
Ok, it kind of works, if there isn't a value in for the delivery day then it stops, and shows an error message, but it isn't pasting the data where it needs to go. Am I missing a line to achieve this, I really want to learn this, but I do feel as if I'm getting too ambitious for my first project.

It started as a simple invoice and ordering list, but I got a bit carried away and it is now nearly fully automatic with the user only having to generate the invoice and transfer that to the ordering sheet, it has a full customer list and product list which is searchable using dynamic arrays direct from the invoice, with a form to input to the customer list, a tally sheet that calculates the weekly order totals if you input the data from the invoice. I'm also currently working on an auto generated list for packing the produce, Eg, customers 1,3&5 ordered apples.

This Macro which I'm stuck on is to transfer from "invoice" sheet to "sheet 1" which is my tally sheet which automatically calculates the weekly and daily totals depending on which column the data is entered into, sorted by day (columns) and product (rows).

I realise that I'm rambling a bit and I really greatly appreciate the help I have received thus far. Thank you.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,537
Office Version
2019
Platform
Windows
Ok, it kind of works, if there isn't a value in for the delivery day then it stops, and shows an error message
Delivery days are D1, F1, H1, J1, L1? What do you want it to do when there is no match?

You mention an "invoice" sheet, but there was no reference to this in your original code, everything is referring to sheet1. This could be why the data is not being copied to the correct place.

The packing list that you also mention appears to be another issue. It would be better to get the first part working before starting additional parts.
 

Trueblue862

Board Regular
Joined
May 24, 2020
Messages
50
Office Version
365
Platform
Windows
The packing sheet is a complete different issue, I've been using it to give myself a break when I start running in circles on this one.

If there is no match then it needs to stop and not progress otherwise data won't be entered where it needs to be.
Sorry, like I said I'm a complete amateur at this, it is meant to be looking for values on sheet invoice and placing them is Sheet 1 which contains my running total macro.
Thank you for your patience.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,913
Messages
5,465,414
Members
406,426
Latest member
slari

This Week's Hot Topics

Top