VBA Code for adding values in 18 rows to 18 values in columns

[craignigel]

Hi

I have set up an Excel SS (2007) to help me with keeping monthly income/expenditure accounts. I have 18 budgets arranged in columns from D to U on Sheet1. I can enter a new debit or credit using columns A, B & C (for the date, detail and amount respectively. A total balance is provided is column V by adding along the row.


"Fluff" on 11 Feb from this forum kindly helped me by writing some VBA code to copy the last row of budget values down one row to the first blank row thus allowing me to then enter a new debit or credit and manually adjusting the relevant budget. I've made some conditional formatting to the data and it all works very well, saving me a great deal of time with paper, pens and calculator!


I would like to be able to add/subtract 18 rows of values (monthly budgets) to the last new row created on sheet 1. These 18 values could be in a list perhaps on a list on Sheet 2 starting say at B4. I need to be able, easily, to change their values from time to time. Is it possible to do this in code and I can place a "button" on sheet 1 to perform the task. I did this to run the macro that Fluff kindly helped with.

This would save me time once a month by automating a task that I now have to do manually for each of the 18 columns.


Would anyone please steer me in the right direction. Should I try to do this this with a macro or not. I can see obvious difficulties in adding values from 18 rows to 18 columns and of course the last row changes each time a new debit/credit is made.


Any help or ideas would be very much appreciated. When I've finished the SS I would be more that happy to make it available to anyone.

Many thanks for reading this.

Nigel

 

[RobertSF]

I confess I'm having a hard time understanding the requirements, but given that this is appears to be about personal accounting, have you considered the MS-Money application that Microsoft now makes available at no charge? https://www.microsoft.com/en-us/download/details.aspx?id=7564

 

[craignigel]

Hello

RobertSF - Thank you for reading my post and I agree it is not easy to follow what I want. I have looked at MSMoney and many other accounting software programs but they don't do exactly what I want but thanks for the suggestion.

I didn't say in my previous posts but I did write a fully automated accounts program in Lotus 123 r 5 and I still use this on my notebook running Windows 7 Ulitmate via the Windows XP Mode. I designed this SS over time and it has many lotus macros that I wrote. It really does everything that I want. However, in the future when I have to used windows 10 or this computer fails I will not be able to use it. So I thought that I should try to learn what I can about Excel and, over time, re-write my account program using it.

Basically, I have a 18 columns of values on Sheet 1:


Column D..............................to Colum U

And 18 rows of values on Sheet 2

Cells B4
.
.
.
to Cells B21

I need to learn how to write VBA code that will add them Eg. B4 to last value in column D, B5 to last value in Colum E etc... and enter the results in the Columns D....to U over-writing the last row of data in the columns.


I will keep persevering and hopefully get there in the end and learn at least something about Excel (may be???).


Thanks for reading this and thanks to Robert again

Nigel

 

[RobertSF]

Oh, I see where you're coming from. I cut my teeth on Lotus, too, many years ago. Also, you may know this, when you record a macro from keystrokes, Excel writes the corresponding VBA code behind the scenes. That's sometimes useful to figure out how to do something. Have you done any programming, enough to learn about loops and if statements?

You'll also need to learn about the Excel Object Model and its hierarchy of objects. For example, Excel itself is an object called Application, and then each workbook is a Workbooks object, and each sheet in each workbook is an object as. So Application.Workbooks(3).Sheets(4).Range("B4:B21").Cells(1).Value refers to the value in Cell B4 (cell one of the range B4:B21) on the fourth sheet in the third workbook (assuming you have three workbooks open).

You can also use names and drop some of the object names and default properties. For example, since the Workbooks object can only belong to the Application object, you can omit typing "Application," and since the default property of Cells is Value, you can omit typing "Value," like this: Workbooks("budget2018.xlsx").Sheets("january").Range("A1:A20").Cells(4)

You described two ranges, one of each sheet. It sounds like the range B4:B21 is a single row you use to enter data? Then this row will update the columns D...U in Sheet1? How do the rows in Sheet1 grow? Or do they remain static? If you can post a screen shot, I might be able to give you more info.

 

[craignigel]

Hi

Thanks Robert for the helpful reply. I'm still at the beginning of my Excel journey. I'm fine with formulae but just started with VBA code. The points that you made about the Excel Object Model is really useful and I now take your point about Macro recording in Excel. I did not realise that it actually converted it to code... that's really useful and will help me get used to it. In Lotus I know the key strokes pretty well and the menus. My program is driven by my own menus that I constructed. However, I have to move on I guess and try to pick up Excel.

I have taken screenshots of my Excel SS Sheet 1 and Sheet 2 but I've just realised that I am too new to this forum to be able to post attachments yet. Can I be allowed to post them please ...just for today may be??

The rows in sheet 1 grow by copying using a simple Macro that Fluff helped me with. I amended it slightly because I changed the SS layout a little and I also installed a COPY button to run the macro. This is it:

Sub Ins()
With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)
.Resize(2, 23).FillDown
.Offset(1).Resize(, 3).ClearContents
End With
End Sub

Then I manually enter date, detail, and £ and then amend the particular budget. (My lotus program does all this from menus).

I will post the screenshots of my new SS if I am able?? These will make it clearer.





Many thanks

Nigel

 

[RobertSF]

I've stuck with old software too. If it works, why change it? I remember the Lotus slash menu in DOS. I never used the Windows version. Here's a bit of trivia you might not know. Microsoft had a spreadsheet before Excel called Multiplan. It was only around a couple of years. I just read that Lotus hasn't been developed since 2002, so I guess it's time to move to Excel.

One thing to know about Excel's macro recorder is that it uses Select instead of Range. Select is the object that represents the actual area you have highlighted (selected) with mouse or cursor, while Range is the object that represents any given area of the spreadsheet, whether it's selected or not. The real difference is that Select is visual -- it's literally like auto-typing -- so it's slower than Range. So use the macro recorder to figure out some code, but remember to use Range instead of Select. They are usually interchangeable.

Until you can post a screenshot, what you can do is post the picture somewhere on the internet, and then link it. You can google "host picture without registration." There are site where you can post a picture without signing up.

I'll check out the code you posted from work later. When you post code, post it between CODE tags. Press the # icon in the toolbar. That way, the code is formatted like the language calls for.

 

[craignigel]

I agree. I am still using my lotus program but one of these days my notebook with Windows7 (with Windows XP mode), the PC will fail and I wont be able to use lotus in any newer version of Windows).

Thanks for the info about Excel's macro recorder and I also note how I should post code on here. Sorry!

I will try to find a way to post my two screenshots (.png) and thanks again.

Nigel

 

[RobertSF]

Here's an example of what I think you want, more or less.

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AAA[/TD]
[TD]BBB[/TD]
[TD]CCC[/TD]
[TD]DDD[/TD]
[TD]EEE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col 01[/TD]
[TD]Col 02[/TD]
[TD]Col 03[/TD]
[TD]Col 04[/TD]
[TD]Col 05[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Lets add sheets 1 and 2, that is, AAA will be 2, BBB will be 5, CCC will be 9, etc., etc.

Create a new workbook that looks like the above. Then press F11 to enter VBA. Insert a module (should be Module 1), insert this code into the module, and press F8 to start stepping through.

Option Explicit

Private Sub UpdateSheet()
    
    Const NumCols = 5
    
    Dim SRow As Integer
    Dim DRow As Integer
    
    SRow = ThisWorkbook.Sheets("Sheet2").Cells(ThisWorkbook.Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row
    DRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row
    
    Dim SourceRange As Range
    Dim DestRange As Range
    
    Set SourceRange = ThisWorkbook.Sheets("Sheet2").Range(ThisWorkbook.Sheets("Sheet2").Cells(SRow, 1), ThisWorkbook.Sheets("Sheet2").Cells(SRow, NumCols))
    Set DestRange = ThisWorkbook.Sheets("Sheet1").Range(ThisWorkbook.Sheets("Sheet1").Cells(DRow, 1), ThisWorkbook.Sheets("Sheet1").Cells(DRow, NumCols))
    
    Dim ndx As Byte
    
    For ndx = 1 To NumCols
        DestRange.Cells(ndx).Value = DestRange.Cells(ndx).Value + SourceRange.Cells(ndx).Value
    Next ndx


End Sub

This is a Subroutine. The other option would have been to make it a Function. The difference is that you use a sub like you use a command. You don't expect a return value. You just expect the computer to do whatever. A function, however, returns a value. If you made this code into a function, you would probably want it to return True or False to indicate whether the function had succeeded or not.

The first line defines a constant, a value that cannot change. The number of your columns may or may not be fixed, but in my example, it is. A benefit of using constants is that a well-named constant tells you want it's for. In this case, you go, "oh, it's the number of columns," whereas if there had just been a 5, you might have wondered, "Five what? What does this number indicate?"

Then we define a couple of Integer variables, SRow for Source Row and DRow for Destination Row. In other words, SRow is the row number that has the values that will be added to Sheet 1, and DRow is the row number where the values from the Source Row will be added to. SRow and DRow are not the best names because they are too abbreviated. What's an SRow? What's a DRow? Unless you know, you can't figure it out.

Having defined those two variables, we now actually put a value in them. That's the next two lines, and now it starts getting complicated.
SRow = ThisWorkbook.Sheets("Sheet2").Cells(ThisWorkbook.Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row

ThisWorkbook.Sheets("Sheet2") -- this refers to sheet 2 in the workbook

Cells(ThisWorkbook.Sheets("Sheet2").Rows.Count, 1) -- the Cells collections contains all the cells and lets us specify which cell we mean. The syntax is Cells(Row Number, Column Number). Here, we use ThisWorkbook.Sheets("Sheet2").Rows.Count to find the number of rows on the sheet. In VBA, collections like Rows, Columns, Cells, Workbooks, Sheets, etc., have a Count property that tells you how many there are, so Rows.Count returns the total number of rows in the sheet (65,536 in older versions, and 1048576 in newer ones). The Column Number is 1, which corresponds to Column A on the sheet, so the expression so far points to cell A1048576 (or A65536 if you have an older Excel version). But we're not done yet because that's the last row on the sheet, not the last row where our number are, so what follows is End(xlUp). This is just like pressing the End key and then the Up Arrow key from the bottom row. You are moved automatically to the last used cell in that column. Finally, we end the line with Row, which returns the current Row number.

We can read the entire line as "In Sheet2 of this workbook, find the last row by moving to the very bottom and then moving up to the first non-empty cell. Then take the row number of that first non-empty cell and put it in SRow. This is our Source Row number.

The next line does the same, except for the other sheet, the Destination.

Now we have two ranges. Think of them like windows into your spreadsheet. One window points to where the data is coming from, and the other window points to where the data is going.

Now let's define a small variable, just a byte, which can count up to 255. Pick the smallest variable type that accommodates your needs. You don't have more than 255 columns, so a byte will do just fine. Otherwise, you'd have to use an Integer, Long, Double, or something else.

Then we have the loop that does the actual work. The For-Next structure is one way that VBA does loops, doing the same steps over and over, making some small controlled change each time. Here's the full syntax. The variables you provide are in italics.

For counter = first To last Step n
    'steps to be done over and over
Next counter

In this case, we go from 1 to 5, and we don't use a Step because it counts one by one by default. The variable ndx first contains a 1, then a 2, all the way through 5. When it adds one more, it becomes 6, and that's over the To limit, so it exits the loop.

We only have one step, add one value to the other value. Note how defining the ranges saves a lot of typings later on, not to mention making things much more clearer. Instead of having to say ThisWorkbook.Sheets("Sheet1").Range(ThisWorkbook.Sheets("Sheet1").Cells(DRow, 1), ThisWorkbook.Sheets("Sheet1").Cells(DRow, NumCols)) all we had to do was say DestRange.

So now the step. DestRange.Cells(ndx).Value = DestRange.Cells(ndx).Value + SourceRange.Cells(ndx).Value

This is almost plain English. "Make the value of the (ndx)th cell in DestRange equal to the sum of itself plus the value in the (ndx)th cell in SourceRange."

You can download this example spreadsheet from http://www.clicketyhome.com/example.xlsm

I hope this helps. We're all still learning.

 

[craignigel]

hi!!! I've been away a few days and amazed at your post!!

It's really great and useful and I'm going to try to amend the code to correspond to my cell references. My sheet 2 has the 18 data items, downwards, in one column. Do I need to use "transpose" somewhere to add it to the 18 items all in one row?

I am going to follow your macro one line at time and try to make adjustments.

Meanwhile, I have the 2 screenshots and I'm trying to post to an URL. ....but still looking around.

I am so grateful for your help Robert.

Many thanks

 

[craignigel]

Hi

I hope these two screenshot links work.....

https://snag.gy/qUl0hL.jpg


https://snag.gy/B27V0X.jpg


You can see the ranges that I working with:

Data in Sheet 1 D to U rolling down a row each time the copy macro is used.

Budgets fixed in C4 to C21

So there are always 18 entries in both.


Thanks for reading (if it works)

Nigel